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.
Alpha 10 Alpha 30
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.