Adding RAND() number to measure and RANKX() it

vilnyts — Wed, 13 Jul 2022 06:37:46 GMT

Hi, everyone.

I have a problem when i am using RANKX() function.

I have a one column table that contains redundant category names (AAA, BBB, CCC.....FFF) and have a measure that counts rows through a table:

Counts =

COUNT(

'table'[Dim]

)

I am going to rank my rows, to do that i have a measure:

RankRows =

RANKX(ALL('table'[Dim]), [Counts] ,, DESC)

When i put those measures (rank and count) and column into a table i have the following:

As you can see categories EEE, FFF have the same counts and as a result the same rank.

I want to get rid of this, i need to get the unique rank for the each row.

To do that, "Counts" measure was modified a bit i added random number and expected to get correct rank, but i have this:

"Counts" was modified like this:

Counts =

COUNT(

'table'[Dim]

) + DIVIDE(RAND(), 100)

Could somebody help or explain way, how to add to measure random number and after RANKX() through this value.

I am expecting from above example something like this:
DDD, 8, 1

CCC, 7, 2

BBB, 6, 3

AAA, 5, 4

EEE, 4, 5

FFF, 4, 6

Thank you in advance.

Have a good day)

Re: Adding RAND() number to measure and RANKX() it

Jihwan_Kim — Wed, 13 Jul 2022 08:01:52 GMT

Hi,

Please check the below measures and the attached pbix file.

I suggest writing an additional measure like below.

Rankalphabet = RANKX ( ALL ( 'Table'[Dim] ), CALCULATE ( MAX ( 'Table'[Dim] ) ),, ASC ) / 100

Counts = COUNT( 'Table'[Dim] ) + [Rankalphabet]

RankRows = RANKX(ALL('Table'[Dim]), [Counts],, DESC)

vilnyts — Fri, 15 Jul 2022 06:22:29 GMT

Hi,

Thanks a lot Jihwan_Kim, it was the solution for me.

Have a good day.