Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
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]
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 🙂
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
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |