Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
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
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.
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] )
)
)
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
sum of what?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
72 | |
67 | |
25 | |
18 | |
12 |