Forum Discussion
What Function should I use for Row Sum?
Question 1) If Part is "A" then sum <Part "B" WH A Qty> & <Part "C" WH A Qty> else <WH A Qty>. What function should I apply in Power BI? Part WH A Qty A 13 B 24 C 56 ...
Let me re-phase my case again. Table below shows Part & Sales Qty
I would like to add custom column to calculate result as per Expected Sales Qty Column.
Condition:
Part B always = Part B Sales Qty + Part D Sales Qty
Part D always = 0
Other Parts = Same Sales Qty
| Part | Sales Qty | Expected Sales Qty |
| A | 12 | 12 |
| B | 45 | 69 |
| C | 67 | 67 |
| D | 24 | 0 |
| E | 52 | 52 |
| F | 45 | 45 |
Hi bensim1973 ,
Please refer:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ghCxOtFKTkCmiSmQMLME851BTHMIAeK7AJlGJkDCAMx1BbJMjSAEiO8G0w4kYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Part = _t, #"Sales Qty" = _t, #"Expected Sales Qty" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Part", type text}, {"Sales Qty", Int64.Type}, {"Expected Sales Qty", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Part]="B" then List.Sum(Table.SelectRows(#"Changed Type", each List.Contains({"B","D"},[Part]))[Sales Qty])
else if [Part]="D" then 0 else [Sales Qty])
in
#"Added Custom"Output:
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group