Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

PROBLEMS WITH RANKX

Hi all,

Enyone else experiencing probles with the RANKX?

I've soon browsed the entire web for a solution, but no matter what DAX I'm trying I get the same minor errors.

My "basic" DAX (at the moment) is as follows:

 

RANK - share =

RANKX(

ALLSELECTED(ORGANIZATION[STORE]) ; [SHARE] ; ; DESC ; Dense

)

 

The purpose is to rank  stores based on share of sales of certain products.

[SHARE] is a meassure.

 

No matter what I try, I get some MINOR errors in the ranking.

 

Stores are placed in correct order, but the ranking differs from time to time.

Sometimes no stores are ranked as #1

Sometimes two stores are given he same ranking allthough share is not the same

Sometmes it just skippes a (random) ranking

etc. (see picture)

 

Any bright minds out there who can help me solve this anoying little bug?

Thanks in advance! 

 

msfugg_0-1611044611599.png

 

4 REPLIES 4
Anonymous
Not applicable

Hello @Anonymous ,

RANK - share =
RANKX ( ALL ( ORGANIZATION ), [SHARE], ,DESC, DENSE )
You can refer to this link .

Anonymous
Not applicable

@Anonymous 

Thanks for your reply & effort, but unfortunately this doesn't work either...

I've read the blog-post, but it just don't add up.

 

I have tried de-tours via both ALL and ALLSELECTED among others, but It never gets closer then 95% correct.

 

Possibly it is a result of problems with the measure (SHARE) that is ranked.

Share is again a function of misc other measures.

Maybe I need to go up the entire line one more time.

Anonymous
Not applicable

Hello @Anonymous ,
Can you please provide the sample data in table format so I can check?
Thanks

Anonymous
Not applicable

Thanks, but I think I finally found the solution.

Format the SHARE as CURRENCY.

Ref. this article - seems to be a general DAX issue

 

My final solution was this:

RANK - share = RANKX(ORGANIZATION[STORE];CURRENCY([SHARE]);;DESC)

(For some reason I also had to skip the Dense to get it correct)

 

https://www.sqlbi.com/blog/marco/2014/07/16/use-of-rankx-with-decimal-numbers-in-dax/

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors