Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
bensim1973
New Member

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 
6 REPLIES 6
bensim1973
New Member

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

 

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:

vcgaomsft_0-1701776108812.png

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

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
New Member

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
New Member

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
Administrator
Administrator

sum of what?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors