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.
Hi All,
I'm new to DAX and Power BI, and have gotten in a bit over my head. Working in Poerw BI Desktop. I have an Inventory table by Part, Customer, and Quantity. For each row in the table, I'd like to determine the Customer with maximum aggregate Quantity for that row's specific Part (note that a Customer may have multiple rows for one Part, that need to be summed, e.g. Part JKL below). If there is a tie, doing something like returning the first Customer is fine. See the below simplified table with desired output "Desired Max Customer". I'd like this to be calculated at the individual row level, and not be impacted by any visual filters or aggregation.
I tried breaking this down into two steps to simplify, by finding the max Quantity first then finding the Customer associated with the max. But I wasn't even able to get a DAX formula to properly return the "Desired Max Value". After many many Google searches I'm stuck. I thought this formula would work for the Max, but it didn't return the value I expected:
Desired Max Value = MAXX(CALCULATETABLE(VALUES(MyTable[Customer]),ALLEXCEPT(MyTable,MyTable[Part])),CALCULATE(SUM(MyTable[Inventory])))
I couldn't get past that first step, or to the actual output I need, which is "Desired Max Customer". Any assistance in pointing me in the right direction would be very appreciated! Thanks.
Part | Customer | Quantity | Desired Max Value | Desired Max Customer |
ABC | A | 564 | 564 | A |
ABC | B | 87 | 564 | A |
ABC | C | 459 | 564 | A |
DEF | D | 1237 | 1237 | D |
JKL | A | 82 | 350 | B |
JKL | B | 150 | 350 | B |
JKL | B | 200 | 350 | B |
JKL | C | 310 | 350 | B |
Solved! Go to Solution.
Hi @mf720
You were certainly on the right track...here is how I would write these calculated columns:
Desired Max Value = CALCULATE ( MAXX ( VALUES ( MyTable[Customer] ), CALCULATE ( SUM ( MyTable[Quantity] ) ) ), ALLEXCEPT ( MyTable, MyTable[Part] ) )
Desired Max Customer = CALCULATE ( FIRSTNONBLANK ( TOPN ( 1, VALUES ( MyTable[Customer] ), CALCULATE ( SUM ( MyTable[Quantity] ) ) ), 0 ), ALLEXCEPT ( MyTable, MyTable[Part] ) )
In Desired Max Customer, FIRSTNONBLANK is just there to break ties (selects the first customer alphabetically).
Do those work for you?
Regards,
Owen
Hi @mf720
You were certainly on the right track...here is how I would write these calculated columns:
Desired Max Value = CALCULATE ( MAXX ( VALUES ( MyTable[Customer] ), CALCULATE ( SUM ( MyTable[Quantity] ) ) ), ALLEXCEPT ( MyTable, MyTable[Part] ) )
Desired Max Customer = CALCULATE ( FIRSTNONBLANK ( TOPN ( 1, VALUES ( MyTable[Customer] ), CALCULATE ( SUM ( MyTable[Quantity] ) ) ), 0 ), ALLEXCEPT ( MyTable, MyTable[Part] ) )
In Desired Max Customer, FIRSTNONBLANK is just there to break ties (selects the first customer alphabetically).
Do those work for you?
Regards,
Owen
Brilliant! Thanks @OwenAuger, I really appreciate your help. Your formulas worked perfectly, that's exactly what I was looking for. Now I just need to stare at them for a while to fully understand how they're working 😉
Regards,
Mike
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |