RANKX function not giving correct results

[Ani26]

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

[Ani26 · Accepted solution]

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

[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]

Ohh God.
My sincere apologies @parry2k. Extremely sorry for that.
I will try to replicate the issue in a sample pbix and will send it to you over email.

[parry2k]

@Ani26 can you make sure in the visual you are using Region from the region dimension not from the fact table, just making sure.

[Ani26]

Yes @parry2k , that is handled correctly.

[parry2k]

@Ani26 to clarify, you are saying if you keep the Region in the visual, it works, and if you remove the region it doesn't work. correct?

[Ani26]

Hi @parry2k 
Rather the opposite.
If I remove the Region field from the visual it works but if I pull the region field it gives incorrect ranking.

[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.⚡

[Ani26]

I 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.)

[smpa01]

@Ani26  can you please create  a sample pbix and provide here?

[Ani26]

Hi @smpa01 
My sincere apologies but I am not getting the option to attach a pbix here. Since this been an organization system I don't have access to save it on a drive and provide the link.

[parry2k]

@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.⚡

[Ani26]

Hi @parry2k 
Thank you for providing the link. I went through the link provided by you but in that the DAX measures are created for fields coming from the same table whereas in my case  I am pulling fields from different dimension tables and measure values from Fact table.