Matrix / Sumproduct in Power BI

[tdeshaye]

Hi all

I have a use case I'm been struggling to solve on my own so I come to seek your help!

My power pivot data table looks like that:

[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD]product[/TD]
[TD]supplier[/TD]
[TD]quantity[/TD]
[TD]order day[/TD]
[TD]receipt day[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]supplier1[/TD]
[TD]45[/TD]
[TD]Monday[/TD]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]supplier1[/TD]
[TD]456[/TD]
[TD]Monday[/TD]
[TD]Thursday[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]supplier1[/TD]
[TD]86442[/TD]
[TD]Monday[/TD]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]supplier1[/TD]
[TD]74[/TD]
[TD]Tuesday[/TD]
[TD]Friday[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]supplier2[/TD]
[TD]784[/TD]
[TD]Wednesday[/TD]
[TD]Friday[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]supplier2[/TD]
[TD]768[/TD]
[TD]Tuesday[/TD]
[TD]Friday[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]supplier2[/TD]
[TD]291[/TD]
[TD]Tuesday[/TD]
[TD]Saturday[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]supplier2[/TD]
[TD]75[/TD]
[TD]monday[/TD]
[TD]Friday[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]supplier2[/TD]
[TD]947[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[/TR]
</tbody>[/TABLE]


And I'd like to have as an output a pivot table which would look like:

[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Order Day[/TD]
[TD]Receive Day[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]87018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]1133[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]1731[/TD]
[TD]86487[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]0[/TD]
[TD]1403[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]0[/TD]
[TD]1701[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD]0[/TD]
[TD]291[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

basically this would the result of SUMPRODUCT (quantity * order_day) and SUMPRODUCT ( quantity * receive_day) but I would be able to apply filters on products and supplier in my pivot table.

thanks a lot for your help

thomas

 

[MagnarH]

Hello Thomas,

If I understood correctly, your table should look like the one below.
This will turn out the Pivot table as your output example.

product	supplier	quantity	Type	Weekday
A	supplier1	45	order day	Monday
A	supplier1	45	receipt day	Wednesday
B	supplier1	456	order day	Monday
B	supplier1	456	receipt day	Thursday
C	supplier1	86442	order day	Monday
C	supplier1	86442	receipt day	Wednesday
D	supplier1	74	order day	Tuesday
D	supplier1	74	receipt day	Friday
E	supplier2	784	order day	Wednesday
E	supplier2	784	receipt day	Friday
F	supplier2	768	order day	Tuesday
F	supplier2	768	receipt day	Friday
G	supplier2	291	order day	Tuesday
G	supplier2	291	receipt day	Saturday
H	supplier2	75	order day	monday
H	supplier2	75	receipt day	Friday
I	supplier2	947	order day	Wednesday
I	supplier2	947	receipt day	Thursday

<tbody>
</tbody>

The quickest way to do this, is to use Power Query and Transform -> Unpivot your Order day and Receipt day columns.

Then just use the result table as your scource for the Pivot table.


I hope this was understandable.

Good luck.

 

[tdeshaye]

yes it was, thanks that will work for my use case!

thomas