RANKX HELP

[LDC20]

Hi There,

I'm having trouble with my RankX function and have tried following tutorials and tips and am not getting the same result

I have a list of names and these names are associated to a number of transactions

I am trying to create a ranking that ranks on the number of transactions against each name

RankingTrans = RANKX(ALL('CCData'[Sales Rep]), [TransCount])

This ranks the data which is fine but the ranking is skipping numbers e.g 1,2,3,4,4,5,5,5,9,10,10,12

How can I stop this from happening?

[harshnathani]

Hi @LDC20 ,

This is because Sales Rep ranked 4 will have same transaction counts. Same would be rank 5,9,10.

What is the end output needed.

kindly share sample data.

Regards,

Harsh Nathani

[LDC20]

Thanks for the reply

In my rankings I dont want it to skip any numbers

[harshnathani]

Hi @LDC20 ,

Create a measure

Randome = RAND()

Then add this measure to your ranking measure

RankingTrans = RANKX(ALL('CCData'[Sales Rep]), [TransCount] + [RAND])

You will get rank as 1, 2 ,3 ,4.2,4.5 ... etc

else do you have any other supporting column like date,.

Then follow this articles

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

else pls follow this solved solution 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Can-t-break-RANK-TIES-cannot-use-RAND/td-p/10...

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[lbendlin]

Create an index column over your numbers instead of using RANKX, or use the DENSE ties option if you insist on using RANKX

https://docs.microsoft.com/en-us/dax/rankx-function-dax#syntax

[LDC20]

I added in the DENSE and its still ranking similar values as the same

See attached screenshot