Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculate Rank based on Slicer

Hi, I'm new to Power BI and trying to calculate the Rank based on a Slicer value.   I am trying to Rank a list of schools based on their A Level performance data but with the flexibility that we c...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi Anonymous 

You create calculated columns which can't change with the slicer.

You could create measure for the rank.

Measures as below

rank by = SUM(T_WP_School_Criteria_Flags_All[ALevel_Performance_Value])

rank = RANKX(ALLSELECTED(T_WP_School_Criteria_Flags_All),[rank by],,ASC,Dense)

rank dynamic = [rank]*[Benchmark Value]

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Maggie,

     

    Thanks for your help.  The KS5 report is almost there but not quite.  If I use the ALevel_Performance_Rank slicer and uncheck the 'Y' in the ALevel_Performance_WP_Flag filter in the table, it does sort dynamically.  However, our users won't know the values for the rank i.e. that 1184 is 40%, so I am trying to get the Benchmark slicer to work.

     

    I am looking for the bottom % of schools ranked by the Benchmark slicer i.e.

    if the user wants to look at the bottom 30% of schools, all schools from Walworth Academy down will be displayed (value 26.52, rank 888, benchmark 0.30).  Here is an example for other benchmarks:

     

    School                                        Value     Rank    Benchmark

    Walworth Academy           26.52    888      0.30

    Highbury Fields School    28.48    1184   0.40

    City of Norwich School    30.32    1481   0.50

     

    Does that make sense?  I'm not even sure if this is possible but I will keep trying :)