Forum Discussion

NB689's avatar
NB689
Helper I
3 years ago
Solved

Help with RANKX

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 up...
  • NB689's avatar
    NB689
    3 years ago

    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)
Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

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's avatar
    NB689
    Helper I
    3 years ago

    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's avatar
      NB689
      Helper I
      3 years ago

      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)