What Function should I use for Row Sum?

[bensim1973]

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
D	23

[bensim1973]

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

[v-cgao-msft]

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

[j_ocean]

Reference to a new table, filter it down to D. Add a dummy column "B". Merge it back in matching part column to dummy column. Then everything is on the right rows for you to do a simple custom column if part = D then 0 else expected sales qty + D.sales.

[faith254]

Alternatively, if you're seeking a calculated column you could use the SUMMARIZE function

SummaryTable =
SUMMARIZE (
    YourTable,
    YourTable[Part],
    "TotalQty",
    IF (
        YourTable[Part] = "A",
        SUM ( YourTable[B Qty] ) + SUM ( YourTable[C Qty] ),
        SUM ( YourTable[WH A Qty] )
    )
)

[faith254]

Not sure if what you're looking for is Running Totals... If so, this video does a great job of explaining it: https://www.youtube.com/watch?v=d-Jk1j2G3nw

[Syndicate_Admin]

sum of what?