How to aggregate rankings using a measure

[Anonymous]

Hi,

I have the folloing ranking information:

Employee	Category	Score
A	1	100
B	1	80
C	1	60
D	1	40
E	1	20
A	2	50
B	2	60
C	2	90
D	2	80
E	2	100
F	2	70
A	3	80
B	3	90
C	3	100

The RANKX measure would allow me to do something like

Category 1
Employee	Score	Rank
A	100	1
B	80	2
C	60	3
D	40	4
E	20	5

Category 2
Employee	Score	Rank
A	50	6
B	60	5
C	90	2
D	80	3
E	100	1
F	70	4

Category 3
Employee	Score	Rank
A	80	3
B	90	2
C	100	1

But I am trying to aggregate rankings based on Employee, i.e.:

Employee A
Category	Score	Rank
1	100	1
2	50	6
3	80	3

Any ideas in how to achieve the above ranking summary using measure? I want everything to be done in measures (because I have other slicers affectings the score results so it is impossible to store it dynamically in a table).

Much appreciated!!!

[Greg_Deckler · Accepted solution]

@Anonymous - After thinking about this a bit more, I came up with this:

Measure 9 = 
VAR __employee = MAX('Table10'[Employee])
VAR __category = MAX('Table10'[Category])
VAR __table = SUMMARIZE(ALL('Table10'),[Employee],[Category],"__score",SUM('Table10'[Score]))
VAR __table1 = FILTER(__table,[Category] = 1)
VAR __table1a = ADDCOLUMNS(__table1,"__rank",RANKX(__table1,[__score],,DESC))
VAR __table2 = FILTER(__table,[Category] = 2)
VAR __table2a = ADDCOLUMNS(__table2,"__rank",RANKX(__table2,[__score],,DESC))
VAR __table3 = FILTER(__table,[Category] = 3)
VAR __table3a = ADDCOLUMNS(__table3,"__rank",RANKX(__table3,[__score],,DESC))
VAR __tableRank = UNION(__table1a,__table2a,__table3a)
RETURN
MAXX(FILTER(__tableRank,[Employee]=__employee && [Category] = __category),[__rank])

See Page 5 of attached.

[Anonymous]

Hi Greg, thanks for your help! May I ask what is the use of MAX function in your measure of '_employee' and '_category'?

[Greg_Deckler]

Perhaps something along the lines of:

Table = 
VAR __table = SUMMARIZE('Table10',[Employee],[Category],"__score",SUM('Table10'[Score]))
VAR __table1 = ADDCOLUMNS(__table,"__rank",RANKX(__table,[__score],,DESC))
RETURN
__table1

You could grab your Employee and Category and filter down __table1 in a measure.