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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors