Ranking Employees against peers within a Table

[shanebo3239]

I'll try to explain this the best I can.  We have multiple employees across multiple job titles that perform multiple services.

Users Table:

UserId | User Name
1         | John
2         | Bob
3         | Susie
4         | Tina
5         | Alan
6         | Tony
7         | Jessica
8         | Carol
9         | Tanya
10       | Jeff

JobTitles Table:

JobTitleId     | Job Title
1     | CSR 1
2     | CSR 2
3     | CSR 3

Services Table:

ServiceID     | ServiceName
1     |  Mopping
2     |  Sweeping
3     | Vacuuming 
4     | Mowing

WorkHistory Table:

ProcessID | UserID | ServiceID | Duration
1 | 1 | 3 | 201
2 | 1 | 4 | 290
3 | 2 | 2 | 380
4 | 2 | 1 | 1200
5 | 3 | 1 | 1260
6 | 2 | 1 | 900
7 | 1 | 2 | 860
8 | 4 | 4 | 201
9 | 5 | 3 | 209
10 | 6 | 2 | 214
11 | 6 | 1 | 217
12 | 6 | 4 | 1290
13 | 7 | 1 | 1500
14 | 8 | 3 | 905
15 | 9 | 1 | 854
16 | 9 | 2 | 802
17 | 10 | 1 | 809
18 | 10 | 3 | 875
19 | 10 | 3 | 901
20 | 1 | 2 | 698
21 | 2 | 1 | 98
22 | 7 | 2 | 202
23 | 9 | 4 | 298
24 | 1 | 3 | 208
25 | 4 | 3 | 1209

EmployeeCareerLevels Table:

UserId | JobTitleId
1 | 2
2 | 2
3 | 1
4 | 3
5 | 3
6 | 2
7 | 1
8 | 1
9 | 2
10 | 2

I have all of the relationships set up between all of the tables.  From here I want to create a single table visualization that looks something like this:

Service Name |  Average Time | Average Time of Peers |  Rank Among Peers | Rank Among All
Sweeping | 239 | 302 | 1 | 3
etc

The slicer on the page would have the User's Name.  So selecting the users name would change the values above.  I've got everything basically except the "Rank Among Peers" and "Rank Among All".  My issue is that the slicer MUST filter this visual.  I just need to know what kind of RANK or RANKX function I should be writing to do what Im trying to accomplish.

[ibarrau · Accepted solution]

Hey there. I think you should play a lilttle with ALL, ALLSELECTED and ALLEXCEP. Add measure like this:

= RANKX(ALLEXCEPT(Table; ColumnsFilter); SUM('Table'[Number]) )

It dependes on what filter you want to be apply.

ALL -> No filter apply

ALLSELECTED -> Avoid filters in the query and accepts the external ones.

ALLEXCEPT -> receive columns that will filter the measure

Regards,

[shanebo3239]

Thank you.  I'll give those a shot.  I've been working with Dax for about 6 months and it still confuses me considerably at times.  But I'll give your samples a shot.