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 can change the benchmark value to re-calculate the Rank.  The original Rank value is based on the MAX(ALevel_Performance_Rank) * Benchmark.

 

Below is a screenshot of the current data but the slicer only changes the Benchmark value in the card and has no effect on the table.

 

 

Is there a way to do this?  Any help would be appreciated please :)

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi, 

    Can you post a sample .pbix file so we can help you easier? Maybe through Google Dropbox?

    Thanks.

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity 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 :)