Deciles and Ranking

[Pippa_Jane]

Hello! 

I was hoping someone could help:

Is there a way to limit the number of ranks in the RANKX column? What I would like to do is have a dataset ranked 1 - 10. I think the only way for me to do this is to create a decile broken down into 10 sections, and then to link the section to a specific rank of 1. EG data between the values of 1-10 will be rank 1. I am struggling to find a function that a. allows me to chose the number of deciles to break the data down into or/and b. force rankx to only assign ranks between 1-10. 

Does anyone have any experience with this?

Many thanks! 

[v-juanli-msft · Accepted solution]

Hi @Pippa_Jane 

Assume your requirement is :

If you have a column with values from 1 ~100,

then assign values(1~10) to a new rank 1

values(11~20) to a new rank 2

If so, 

Create columns

rank index = RANKX('Table (2)','Table (2)'[data],,ASC,Dense)

% = [rank index]/ MAX('Table (2)'[rank index])

new rank = SWITCH(TRUE(),[%]<=0.1,1,[%]<=0.2,2,[%]<=0.3,3,[%]<=0.4,4,[%]<=0.5,5,[%]<=0.6,6,[%]<=0.7,7,[%]<=0.8,8,[%]<=0.9,9,[%]<=1,10)

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.

[Car47]

Hello, this solution worked great and I'm wondering if you have an opinion on how to further refine it. I have a dataset that I have ranked using your methodology, and it's for 4 years' worth of data. What I would like to do, whether in the table or on a sheet in PowerBI with a slicer, is to perform the ranking only for the last 12 months. (which would re-calculate as I bring in new monthly data. Any thoughts how to accomplish this?

[Pippa_Jane]

Thank you very much! This worked perfectly 🙂