Forum Discussion

Zain26's avatar
Zain26
Icon for Advocate II rankAdvocate II
8 years ago
Solved

Second and Third Maximum Amount and Corresponding Text

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 in...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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 )
)
questions's avatar
questions
Icon for Helper I rankHelper I
7 years ago

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: