SumProduct with IF Condition

[Anonymous]

I have multiple PO numbers in one column and the corresponding Order Quantity and Weight of each item in that PO. Now to calculate the total weight of the PO, I need to multiply Order Quantity*Weight for each item and sum it up if there are multiple items in the PO. In excel, Im using a sumproduct formula with If condition. for eg .

SUMPRODUCT(IF($F$2:$F$29190=F2,$S$2:$S$29190),IF($F$2:$F$29190=F2,$T$2:$T$29190))

F Column = PO Number

S Column = Order Quantity

T Column = Weight

How do I replicate this in Power BI?

[Anonymous]

Hi @PhilipTreacy : The results are as expected, Thank You! Is there a way I can have the remaining columns (i.e Qty, Weight) also in the final table/view ?

[PhilipTreacy]

Hi @Anonymous 

How exactly do you want the final table to look? 

Using my source data, would you end up with 6 rows in which case, how do I show the weight and qty for each item in the order?

Or if you want 22 rows then the Order Weight is duplicated for each row of the respective order.

Regards

Phil

[Anonymous]

Hi @PhilipTreacy : I would want the Order Weight to be duplicated for each row for the respective PO Number. For eg. in your database : For PO Number 1, 58 is the order weight which will be duplicated for all the rows for that PO ( 4 rows in case of PO number 1)

[PhilipTreacy]

Hi @Anonymous 

Download sample PBIX file

OK you just need to do a table join so that the result of the grouping is merged with the original data - basically your merging the table with itself.

= Table.NestedJoin(#"Changed Type", {"PO Number"}, #"Grouped Rows", {"PO Number"}, "Order Weight", JoinKind.LeftOuter)

Then expand the table in the column to give the Order Weight

Regards

Phil

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Download PBIX file with example shown below

This is my dummy data

In Power Query you can multiply the weight by the quantity to give a new column called (e.g.) Qty Weight.

Then group by the PO Number column (right click on the column header and click Group By)

Then select sum for the aggregation on the newly created Qty Weight column (or whatever you want to call the column)

Final result 

Regards

Phil