Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Hello @Anonymous ,
RANK - share =
RANKX ( ALL ( ORGANIZATION ), [SHARE], ,DESC, DENSE )
You can refer to this link .
@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.
Hello @Anonymous ,
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/
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |