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) gro...
  • 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] )

     

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] )