Forum Discussion
Issues with RANKX
Dear Members!
I have been assigned to build up a database in SharePoint and for the graphical surveys we would use Power BI. have never used this software before so I'm trying to get familiar with it. I'm struggling now with achieving the proper ranking in my document (in the table -name of it is "Table1"- that I've created). I'm just testing this command, because I assume that the usage of this command can be a root cause of a further problem that I'm still facing with.
I defined the "Value" variable on my own (I just filled out the variable with random integers) and I had the feeling that the values of the variable need to be converted into a measure variable first ("Value" -> "Total_value").
So I created an other variable ("Total_value") shown below:
Total_value = SUM(Table1[Value])
Thus I'd have liked to achieve a proper ranking for the "Total_value" variable. Unfortunately I'm not allowed to upload screenshots of the issue, but I share with you the result of the ranking: 35 (1), 24 (2), 34 (2), 21 (4), 19 (5), 13 (6), 10 (8), 9 (9), 8 (10), 6 (11), ..
As you can see, it's not correct (you can find the ranking order in the brackets -> two values were ranked to the 2nd place and thus the 3rd place is missing, plus the 7th place is missing, too).
I tried to rank the values of the “Total_value” variable as follows:
Ranking_value = RANKX(ALL(Table1), [Total_value])
I can summarize the issue in a table-form:
Name | Value | Total_value | Ranking_value |
Cx | 35 | 35 | 1 |
Ca | 24 | 24 | 2 |
Vf | 34 | 34 | 2 |
qa | 21 | 21 | 4 |
íy | 19 | 19 | 5 |
Dsb | 13 | 13 | 6 |
Bvx | 10 | 10 | 8 |
Bxe | 9 | 9 | 9 |
H | 8 | 8 | 10 |
W | 6 | 6 | 11 |
Nbg | 4 | 4 | 12 |
F | 3 | 3 | 13 |
V | 2 | 2 | 14 |
Does anyone have an idea what can be the problem, and could you please help me out?
Many thanks.
Regards,
Fruzsina
Hi, Gombosf , you might want to try this measure
Rank = RANKX(ALLSELECTED(Table1[Name]), [Total_value])In fact, the mechanism of RANKX() under the hood is way much trickier than it appears. Here are some links for your further study if you're looking to master this most frequently-used function in DAX and to put it in your quiver.
https://docs.microsoft.com/en-us/dax/rankx-function-dax
https://www.sqlbi.com/articles/use-of-rankx-in-power-bi-measures/
Gombosf , Rank is context-sensitive. When you take the table the level of the table where it going to create rank from.
Try a measure like
Ranking_value = RANKX(ALLSelected(Table1[Name]), [Total_value])
refer: https://www.youtube.com/watch?v=DZb_6j6WuZ0
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
3 Replies
Hi, Gombosf , you might want to try this measure
Rank = RANKX(ALLSELECTED(Table1[Name]), [Total_value])In fact, the mechanism of RANKX() under the hood is way much trickier than it appears. Here are some links for your further study if you're looking to master this most frequently-used function in DAX and to put it in your quiver.
https://docs.microsoft.com/en-us/dax/rankx-function-dax
https://www.sqlbi.com/articles/use-of-rankx-in-power-bi-measures/
Gombosf , Rank is context-sensitive. When you take the table the level of the table where it going to create rank from.
Try a measure like
Ranking_value = RANKX(ALLSelected(Table1[Name]), [Total_value])
refer: https://www.youtube.com/watch?v=DZb_6j6WuZ0
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415Dear CNENFRNL and amitchandak !
Thank you for your help.
I think the main issue with the RANKX command was that I didn't use a separate table for the command. I feel this reason responsible for the wrong ranking order.
I hope that you can see the picture attached.
If this is the reason for the wrong ranking order, then I don't really understand the behaviour of the RANKX command (I have no clue why is it necessary to create a brand new table for having this ranking in a right way). Maybe this comes simply from the nature of the command. 🙂
Many-many thanks.
