Forum Discussion
Deciles and Ranking
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!
Hi Anonymous
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
MaggieCommunity 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.
3 Replies
v-juanli-msftCommunity Support
Hi Anonymous
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
MaggieCommunity 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.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?