RANKX does not work for me. Any help appreciated

[LukasV89]

Helo everybody,

I have been fighting with the RANKX function. My data looks like this:

Name                Member_ID              Tickets Closed       Tickets Worked     Survey Score           Date

John B               123456                              35                    20                              88%                  1/1/2017

John B               123456                              12                    10                              98%                  1/2/2017

ALice N              234567                              22                    15                              95%                  1/1/2017

ALice N              234567                              14                    23                              75%                  1/2/2017

What I am trying to achieve is to create four columns:

1) Ranking by CLosed TIckets

2) Ranking by TIckets Worked (Touches)

3) Ranking by Survey Score

4) Total Ranking (Smalles product of all rankings = Number 1)

I also have a date slicer so I can look at the these metrics by specific days. I have had a similar data set where I had for each record 1 row and it worked. For this report I am uploading an aggregate data set by Name, and date. I am not sure if this really has anything to do with this.

At this point, I have tired many different formulas and the main issue I was running into was that I got 1's for every single person meaning I ranking each person seperately. This was my original formula:

Name of my table is TEST:

ranking 6 = RANKX(ALLSELECTED(TEST[Member_ID]),CALCULATE(SUM(TEST[Closed Tickets])))

Then I did some research and I have found this:

MM Closed tickets = Sum(TEST[Closed Tickets])

Tickets Ranking = if(HASONEVALUE(TEST[Member_ID]),RANKX(ALLSELECTED('TEST'),[MM Closed Tickets]),BLANK())

I get a ranking order now but it is partially wrong. Also, when I select specific days, it is working correctly. When I select all days, the rankings are off. Please see the attached screenshot on the top

I would really appreciate any advice or help on this one. Thank you.

Lukas

[v-caliao-msft · Accepted solution]

Hi @LukasV89,

You could use the DAX expression below to create rank column.
rankbyclosed = RANKX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Tickets Closed],,)
rankbyWorked = RANKX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Tickets Worked],,)

rankbySurvey = RANKX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Survey Score ],,)

Regards,

Charlie Liao