Second and Third Maximum Amount and Corresponding Text

[Zain26]

Hello All,

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 since Gamma is 50+25 and 70 as the third Max as Beta has 50 and 20 as Amount, 
After this I want to get the corresponding Mapping values i.e. Alpha, Beta, Gamma in separate card visuals which is why i guess this will be done using separate measure. 
ANy help will be appreciated

[Zain26]

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

[Zain26]

Alpha             10
Alpha             30
Beta               20

[Zain26]

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 since Gamma is 50+25 and 70 as the third Max as Beta has 50 and 20 as Amount, 
After this I want to get the corresponding Mapping values i.e. Alpha, Beta, Gamma in separate card visuals which is why i guess this will be done using separate measure. 
ANy help will be appreciated

[Zain26]

Beta               50

Alpha             40
Gamma          25
Gamma          50

[Zain26]

Beta               50

Alpha             40
Gamma          25
Gamma          50

[Zain26]

Beta               50

Alpha             40
Gamma          25
Gamma          50

[Zain26]

Alpha             40
Gamma          25
Gamma          50

[questions]

@Zubair_Muhammad 

Thanks, below is very clear.

However, I would like to further develop an visual to identify the weekly trend for the 2nd top mapping in different weeks by Region eg. Gamma. in Asia region in Week 1, 2 & 3

I try to put the rank into the visual level filter and choose the value as "2", but the graph didnt reponse accordingly.

Would you please explain 1) How to rank by the criteria by region & Week 2)develop the weekly trend for the 2nd top mapping?

summary:

[Zubair_Muhammad · Accepted solution]

Hi @Zain26

First define a MEASURE to RANK the Mapping Values..

RANK =
RANKX (
    ALLSELECTED ( Table1[Mapping] ),
    CALCULATE ( SUM ( Table1[Amount] ) ),
    ,
    desc,
    DENSE
)

Now you can get the 1st , 2nd and 3rd mapping values as

FirstMappingValue =
CALCULATE (
    MAX ( Table1[Mapping] ),
    FILTER ( ALLSELECTED ( Table1[Mapping] ), [RANK] = 1 )
)

SecondMappingValue =
CALCULATE (
    MAX ( Table1[Mapping] ),
    FILTER ( ALLSELECTED ( Table1[Mapping] ), [RANK] = 2 )
)

[Zubair_Muhammad]

@Zain26

And you can get 2nd and 3rd Maximum Amounts using this pattern

File attached as well

Second Max Amount =
VAR secondMapping = [SecondMappingValue]
RETURN
    CALCULATE ( SUM ( Table1[Amount] ), Table1[Mapping] = secondMapping )