Dynamic Ranking

[NottsAnalyst]

Hi there!

I'm looking to calculate the ranked position of each local authority based on two parameters - the comparator group (through a dropdown slicer) and the metric. I have had some success previously with RANKX but I believe RANK is now the preferred function and I was getting some unreliable rankings with RANKX in this particular report. 

I have an overview table with a different metric on each row. In this table I need to show the ranked position for Nottingham for the latest year when compared to the other local authorities in the selected comparator group.

On a separate page I need to show the ranked position of each local authority in the comparator group over time (where both the metric and comparator group are selectable in a dropdown slicer), similar to this table:

I was wondering whether someone would be able to help with the DAX required to make the RANK function work for this scenario.

I have uploaded the .pbix file here - https://www.dropbox.com/scl/fi/eekj6nirqx4z5wnvs14cy/Benchmarking-Report.pbix?rlkey=244jpvujrkd8vbyj... 

Thank you!

[v-dineshya · Accepted solution]

Hi @NottsAnalyst ,

Thank you for reaching out to the Microsoft Community Forum.

As you mentioned you want to show the ranked position of Nottingham for the latest years data (2024/25) based on the [SAC Rate Nottingham This Year] measure. And also need this for the previous year (2023/24) .

Please refer below output snap and attached .PBIX file,

Note: I took sample data from my side, please deploy my measures in your final resport, you will get desired output.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @NottsAnalyst ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @NottsAnalyst ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[FBergamaschi]

Hi @NottsAnalyst 

I download the pbix but please I need some guidance on what you want to achieve, based on the visual I found in the pbix itself

Can you please show an image here and comment it so I understand what ranking you need, based on the image?

Thanks

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[NottsAnalyst]

Hi @FBergamaschi 

Thank you for looking at this.

Here is an image of a table I'm trying to replicate:

I need to show the ranked position of Nottingham for the latest years data (2024/25) based on the [SAC Rate Nottingham This Year] measure. I will also need this for the previous year (2023/24) to show direction of travel. The ranked position will differ depending on the comparator group chosen, for example the CIPFA group contains 16 local authorities while there are 152 nationally. All other figures, except for the comparator group rate and the rankings should remain static regardless of which comparator group is selected, as these are the Nottingham only figures. 

Please let me know if you need any further information.

Thanks again!

[FBergamaschi]

Please can you indicate what you are expecting to see? Please edit the picture showing clearly what the rank result should be and what values I should base the calculation on

Sorry but without a clear indication I cannot understand what I should do and I do not know the data

Thanks

(please send me a direct message if you do not see my reply, there are hundreds of mails here)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI