cancel
Showing results for
Did you mean:
Regular Visitor

## Max of Sum of Amount and Corresponding values

Hello

Bit inexperienced with PowerBI, I am trying to calculate the Max, 2ndMax and 3rdMax amount based on the following columns. I want the Max of Sum of Amount for each Mapping, then I intend to print out the text against which I have calculated the Max and 2ndMax values as well in separate Card Visuals.

Mapping      Amount
Alpha             10
Alpha             30
Beta               20
Beta               50
Alpha             40
Gamma          25
Gamma          50

The following query gave me the Max Amount for a Mapping i.e. 80 (Alpha)

`Var maxAmount= MAXX(CALCULATETABLE(VALUES(table[Mapping]),ALLEXCEPT( table,table[Mapping])),CALCULATE(SUM(table[Amount])))`

I want to get 75 as the second Max value and 70 as the third Max, as separate Measures.
After this I want to get the corresponding Mapping values i.e. Alpha, Beta, Gamma in separate card visuals.

2 REPLIES 2
Microsoft

## Re: Max of Sum of Amount and Corresponding values

HI @Zain26

I get why your Max for Alpha is 80 (10 + 30 + 40), but I can't see where you work out that 75 is the 2nd Max

Proud to be a Datanaut!

Regular Visitor

## Re: Max of Sum of Amount and Corresponding values

Hi @Phil_Seamark
Apologies for the poor formatting of the demo columns. 2nd Max would be 75 because Gamma (50+25), and accordingly 3rd Max would be 70 as Beta has values (50+20)

Announcements

#### Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors