Forum Discussion
How to aggregate rankings using a measure
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 ...
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.
Greg_Deckler
Community Champion
Community ChampionPerhaps 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.