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:

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

<tbody>
</tbody>

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

Day	Order Day	Receive Day
Monday	87018	0
Tuesday	1133	0
Wednesday	1731	86487
Thursday	0	1403
Friday	0	1701
Saturday	0	291
Sunday	0	0

<tbody>
</tbody>

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