How to do Ranking

[reh169]

I have a lot of compliance data that I am graphing by the delta. I have 55 different people I am trying to rank based on their compliance. How do I set up ranking in Power Bi desktop?

[v-huizhn-msft]

Hi @reh169,

It's hard to provide the detailed solution without sample table, you'd better share sample table and list expected result for further analysis. For ranking, we usually use RANKX function.

Best Regards,
Angelia

[reh169]

@Angelia, Ok  so being more specfic, I have a bunch of regions that all have delta's and I want to rank them by the sum of that delta. So I have a table built that has Region, then my delta, then my rank of that delta but I keep getting 1 for all my ranks. I am not sure what I am doing wrong. This is my measure, please let me know what Iam doing wrong or what I need to change. Thank you so much!

Admin_Rank = rankx(filter(all('New Lease'),'New Lease'[Admin_Fee_Delta]),CALCULATE(sum([Admin_Fee_Delta])),0)

[hthota]

@reh169

I think this would help you.

Dax Function: Admin Rank = RANKX(ALL(Sheet1[Region]),Sheet1[Sum of Admin_Fee_Delta],,DESC)

[reh169]

I get an error using that verbage and I copied directly.

To get a result this is what I had to change it to 

Admin_Rank = RANKX(ALL('New Lease'[Region]),sum('New Lease'[Admin_Fee_Delta]),0)

And everything is still 1 for the ranking

[hthota]

Look into this report, you may get the exact understanding.

Link: https://app.powerbi.com/groups/me/reports/48585e73-1ed1-44ff-b400-4d241bbb652d?ctid=470e1ac4-5b55-48...

[reh169]

The link is opening a page and I log in but it never loads.

Why in your version are you using sheet1 and why will mine not accpet that? Same with desc?

[hthota]

Make sure that the Rank is Measure in your report.

[reh169]

That is what I am trying to create with my formula, the rank...am I missing a step before this one?

[hthota · Accepted solution]

Create a new Measure as

Sum of Admin_Fee_Delta=sum('New Lease'[Admin_Fee_Delta])

and Create a new measure as

Admin_Rank = RANKX(ALL('New Lease'[Region]),'New Lease'[Sum of Admin_Fee_Delta],,DESC)

I hope it would work.

[reh169]

It worked, thank you so much!!!