Forum Discussion

bensim1973's avatar
bensim1973
New Member
2 years ago

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? 

 

PartWH A Qty 
A13 
B24 
C56 
D23 
Need Help

6 Replies

  • faith254's avatar
    faith254
    New Member
    2 years ago

    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] )
    )
)
  • bensim1973's avatar
    bensim1973
    New Member
    2 years ago

    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

     

    PartSales QtyExpected Sales Qty
    A1212
    B4569
    C6767
    D240
    E5252
    F4545

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
      j_ocean
      Helper V
      2 years ago

      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.