Forum Discussion

vilnyts's avatar
vilnyts
Frequent Visitor
4 years ago
Solved

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

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)
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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)

     

2 Replies

  • vilnyts's avatar
    vilnyts
    Frequent Visitor
    4 years ago

    Hi,

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

    Have a good day.

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    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)