how to show the ranking of data and removing the ties using RANKX function

[da26]

Hi!

Can someone help me how to display the ranking of data based on the count?

And what if there are ties, how to display their ranking? I've already tried the RANKX function but it only displayed Rank 1 on all the data.

Thank you in advance. 🙂

[Anonymous]

Hi @da26

Try the following

1. Create a measure called sumCount = sum( yourtable[CountofHN] )

2. Create a measure called 

    Rank  =

     IF (
HASONEVALUE ( yourtable[Diagnosis] ),
RANKX ( ALL ( yourtable[Diagnosis] ,yourtable[CountofHN]),[sumCount],,desc,dense)
)

If this works please accept this as a solution and also give KUDOS.

Cheers

CheenuSing

[Sean]

http://community.powerbi.com/t5/Desktop/How-to-Rank-a-list-based-on-2-values-double-rankX/m-p/44076#...

[Sean]

Post your Measure formula

[da26]

[Sean]

Rank =
IF (
    HASONEVALUE ( IPD_CurrentOpenStatus[Diagnosis] ),
    RANKX (
        ALL ( IPD_CurrentOpenStatus[Diagnosis] ),
        CALCULATE ( COUNTA ( IPD_CurrentOpenStatus[HN] ) ),,
        ASC,
        DENSE
    )
)

@da26   How about now?

[da26]

Hi @Sean

Still error. I'm sorry.

But thank you so much for immediate response. 😉

[Sean]

How is you data organized - post some sample of what the table looks like?

[da26]

Hi @Sean

I already found the solution in ranking. 🙂

But what I am trying to know now is there any way to remove the ties in ranking? As you see here, when there is a tie, the ranking was just like this.

Thank you again. 🙂