Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wd6ae
Frequent Visitor

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.

 

Rank.JPG

 

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

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @wd6ae 

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]

Capture9.JPG

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.

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 🙂

Anonymous
Not applicable

Hi, 

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

Thanks.

Here is a link to the .pbix file on Google Drive:

 

https://drive.google.com/open?id=1ncdzxpsNVc5kr_XTBB0SPOzq_vh1ijPN

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.