cancel
Showing results for
Search instead for
Did you mean:
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.

 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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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
2 REPLIES 2
Highlighted
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
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

#### Power Platform Community Conference

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

#### Experience what’s next for Power BI

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

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### 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