cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Return the value associated with the Max of an aggregate

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. 

 

PartCustomerQuantityDesired Max ValueDesired Max Customer
ABCA564564A
ABCB87564A
ABCC459564A
DEFD12371237D
JKLA82350B
JKLB150350B
JKLB200350B
JKLC310350B
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Return the value associated with the Max of an aggregate

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


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

2 REPLIES 2
Highlighted
Super User I
Super User I

Re: Return the value associated with the Max of an aggregate

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


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted
Regular Visitor

Re: Return the value associated with the Max of an aggregate

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors