Forum Discussion
Helper IIIRANKX function not giving correct results
Hi Team,
Wish you all a very happy new year !!
Need your help today with one of the requirement.
We have a data model in Power BI with one fact and few dimensions. The requirement is we are pulling fields from different dimensions and the measures from the fact table. Now what we need is to create a measure to show the ranking based on the Sales% value and to show only the top 10.
To quickly summarize, the table looks something like this
| FName | Provider | Region | Sales | Sales% |
| Angela | NYK | EMEA | 110.2 | 2.5 |
Since this is a kind of a dimensional modelling, I used the crossjoin function and created the DAX measure like below.
TF_Rank_QTD_Region =
var top10 = IF(
NOT ISBLANK([Sales%]),
rankx(CROSSJOIN(ALLSELECTED('FProvider'[FName]),ALLSELECTED('Provider'[Provider]),ALLSELECTED('Region'[ Region])),[Sales%],,DESC,Dense)
)
RETURN
IF(top10 <= 10 , top10 , BLANK())
But when I pull this measure in the above table I get repeated ranks based on each region like below.
| TF_Rank_QTD_Region | FName | Provider | Region | Sales | Sales% |
| 1 | Angela | NYK | EMEA | 110.2 | 2.5 |
| 1 | Jeme | NYK | ASIA | 78.3 | 1 |
| 1 | Chil | NYK | AMERICAS | 31.5 | 1.5 |
| 2 | Weren | NYK | EMEA | 23.6 | 2.1 |
| 2 | Brad | NYK | ASIA | 1.6 | 0.8 |
| 2 | Sach | NYK | AMERICAS | 1.5 | 1.5 |
| 3 | Sewr | NYK | EMEA | 2.7 | 1.4 |
| 3 | Venua | NYK | ASIA | 4.6 | 0.4 |
| 3 | Georg | NYK | AMERICAS | 2.5 | 0.3 |
| 4 | Assen | NYK | EMEA | 12.2 | 1.1 |
| 4 | Amwy | NYK | ASIA | 6.4 | 0.3 |
| 4 | Denia | NYK | AMERICAS | 1.2 | 0.3 |
| 5 | Fillo | NYK | EMEA | 53.4 | 1.1 |
| 5 | Richi | NYK | ASIA | 0.3 | 0.3 |
| 5 | Cinthisa | NYK | AMERICAS | 3.7 | 0.3 |
I tried many different combinations but still the rank repeats. The relationship from the dimensions to the fact table is one to many.
The required output is unique ranks based on Sales% in desc order.
| TF_Rank_QTD_Region | FName | Provider | Region | Sales | Sales% |
| 1 | Angela | NYK | EMEA | 110.2 | 2.5 |
| 2 | Weren | NYK | EMEA | 23.6 | 2.1 |
| 3 | Sewr | NYK | EMEA | 2.7 | 1.4 |
| 4 | Assen | NYK | EMEA | 12.2 | 1.1 |
| 5 | Fillo | NYK | EMEA | 53.4 | 0.9 |
May I understand if I am doing something wrong? Any help will be highy appreciated.
Also kindly let me know if any additional information required.
Regards,
Ani
Hi All,
Sorry for a delayed reply.
I got this resolved. Actually the issue was Column Region had a sort by on it by another column which was actually causing the ranking to give incorrect results. As soon as I removed the sorting by another column, the DAX formula (in the description) gave me the correct results.
parry2k and smpa01 thank you so much for help and assistance.Thanks.
Ani
13 Replies
- parry2k
Super User
Ani26 read this post to get ranked by category and subcategory and the same applies in your situation. How to use RANKX in DAX (Part 2 of 3 – Calculated Measures) - RADACAD
✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- parry2k
Ani26 ok try this measure:
TF_Rank_QTD_Region = RANKX ( SUMMARIZE ( ALLSELECTED ( YourFactTable ), 'FProvider'[FName], 'Provider'[Provider], 'Region'[ Region] ), [Sales%], , DESC )Tweak it as you see fit, but first, check if you get the correct rank.
✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Ani26I tried the DAX measure but still no change. The thing I noticed is , till the time I don't pull in the Region field the rank shows up correctly. As soon as I pull the Region field, the ranks duplicates itself as per the unique values in the region table (that is 3 times.)
- parry2k
Ani26 you keep on referring me smpa01 which is cool. Hmm, very hard to tell what is going on. Can you create dummy pbix file and send me via email, my email is in my signature.
✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
- Ani26
Hi All,
Sorry for a delayed reply.
I got this resolved. Actually the issue was Column Region had a sort by on it by another column which was actually causing the ranking to give incorrect results. As soon as I removed the sorting by another column, the DAX formula (in the description) gave me the correct results.
parry2k and smpa01 thank you so much for help and assistance.Thanks.
Ani
