Forum Discussion

TOK's avatar
TOK
Icon for Helper II rankHelper II
4 years ago
Solved

Get Value from second highest rank per multiple dimensions

Hello,

I am struggeling using Rank function with multiple dimensions.

I've got three dimensions and one measure.

 

All I want to do is to get the second highest value (Rank = 2) grouped by all three dimensions. 

 

It is also very unfortunate that I can't use MAXX function as it is required to be the second highest value.

Thanks for your help!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

    Value total: = 
SUM( Data[Value] )
    Rank two value: = 
VAR ranktwotable =
    FILTER (
        ADDCOLUMNS (
            ALLSELECTED ( Data ),
            "@ranking", CALCULATE ( RANKX ( ALLSELECTED ( Data ), [Value total:],, DESC ) )
        ),
        [@ranking] = 2
    )
RETURN
    SUMX ( ranktwotable, Data[Value] )

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

    Value total: = 
SUM( Data[Value] )
    Rank two value: = 
VAR ranktwotable =
    FILTER (
        ADDCOLUMNS (
            ALLSELECTED ( Data ),
            "@ranking", CALCULATE ( RANKX ( ALLSELECTED ( Data ), [Value total:],, DESC ) )
        ),
        [@ranking] = 2
    )
RETURN
    SUMX ( ranktwotable, Data[Value] )