cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
msfugg
Frequent Visitor

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
TarunSharma
Resolver III
Resolver III

Hello @msfugg ,

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

@TarunSharma 

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.

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

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
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors