Help with RANKX

[NB689]

I have looked through some other threads and articles online but I can't sort out what my issue is that I'm having with RANKX.

My goal is to have a ranking that ranks by user ID, and will auto update the ranking based on the filters applied to my table.

I have am using this formula for my RankX: 

RANKX = RANKX(ALLSELECTED('Non Overlapping') , [Average Personal Time],,DESC,Dense )

Here you can see the results for my "RANKX" column aren't coming out correctly. My intended output would be row 1 to be 1, then 2, 3, etc. 

Thank you

[Jihwan_Kim]

Hi,

I cannot know how your datamodel looks like, but please try something like below.

Or, please share your sample pbix file's link.

RANKX measure =
RANKX (
    SUMMARIZE (
        ALLSELECTED ( 'Non Overlapping' ),
        'TableName1'[Group],
        'TableName2'[Region],
        'TableName3'[Division],
        'TableName4'[UserID],
        'TableName5'[Specialities]
    ),
    [Average Personal Time],
    ,
    DESC,
    DENSE
)

[NB689]

Here's what our data looks like. The file is rather large right now and hase some sensitive data, but if you need more information I can provide that.

The measure for Average Personal Time looks like this:

Average Personal Time 3 = IFERROR( 
    SUM('Non Overlapping'[After Hours]) / 
    DISTINCTCOUNT('Non Overlapping'[Login Number]) / 
    (CALCULATE(DISTINCTCOUNT('Non Overlapping'[User ID]), FILTER('Non Overlapping', 'Non Overlapping'[After Hours]>0)) +0)
, 0)

[NB689 · Accepted solution]

This took some trial and error, but I ended up solving my issue. One change I am going to make is I would like for the ranking to not duplicate the ranking in the event of a tie and I would like the ranking to update when I filter the data, but other than those changes it looks like this is working.

Rank X Personal Time = 
RANKX(ALL('Non Overlapping'[User ID]), CALCULATE(SUM('Non Overlapping'[After Hours] ) , ALLEXCEPT('Non Overlapping', 'Non Overlapping'[User ID])),,DESC,Dense)