Forum Discussion
SumProduct with IF Condition
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?
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
5 Replies
- PhilipTreacy
Super User
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
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
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)