cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Zain26 Regular Visitor
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
Phil_Seamark Super Contributor
Super Contributor

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Zain26 Regular Visitor
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)

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 201 members 2,281 guests
Please welcome our newest community members: