Forum Discussion
Help with RANKX
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)
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)
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)