Forum Discussion

ChuckChuck's avatar
ChuckChuck
Helper I
7 years ago
Solved

Dynamic RANKX

 

Hello,

I have a RANKX measure that works on the entire list of customers in my table, but I need to be able to apply a slicer and have the RANKX measure adjust dynamically based on the applied slicer. I've been able to get it to work in each individual scenario (entire table-no slicer added or sliced table), but I can't find a scenario that lets me have it both ways - i.e. ranked for the entire table and then re-ranked when the slicer is used. Below is the version that is working for the entire table. When I use the slicer I want the amount that is being ranked to be reduced to only the Sales Region selected and then the rank applied to that new adjusted total. So if I slice by the West region, Customer ABC123 would go from ranked 2nd to ranked 1st. 

 

 

 

  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi ChuckChuck,

     

    Please refer to below measure:

    M_Amount =
CALCULATE (
    SUM ( Table_2[Amount] ),
    FILTER (
        ALLSELECTED ( Table_2 ),
        Table_2[Customer] = SELECTEDVALUE ( Table_2[Customer] )
    )
)

M_rank = RANKX(ALLSELECTED(Table_2),[M_Amount],,DESC,Dense)

     

    Best regards,

    Yuliana Gu

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi ChuckChuck,

     

    Please refer to below measure:

    M_Amount =
CALCULATE (
    SUM ( Table_2[Amount] ),
    FILTER (
        ALLSELECTED ( Table_2 ),
        Table_2[Customer] = SELECTEDVALUE ( Table_2[Customer] )
    )
)

M_rank = RANKX(ALLSELECTED(Table_2),[M_Amount],,DESC,Dense)

     

    Best regards,

    Yuliana Gu