Return the value associated with the Max of an aggregate

[mf720]

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

[OwenAuger · Accepted 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

[mf720]

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