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 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

  • 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)

3 Replies

  • 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)