topic PROBLEMS WITH RANKX in DAX Commands and Tips

PROBLEMS WITH RANKX

Anonymous — Tue, 19 Jan 2021 08:24:44 GMT

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!

Anonymous — Tue, 19 Jan 2021 08:56:20 GMT

Hello Anonymous ,

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

Anonymous — Tue, 19 Jan 2021 11:37:39 GMT

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 — Tue, 19 Jan 2021 11:40:36 GMT

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

Anonymous — Tue, 19 Jan 2021 12:35:06 GMT

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)