Writing a ranking measure

[butlerrbrian]

I need to rank a team of agents on several different KPI to arrive at an overall ranking.

As a part of building the base measure, I am using the formula below, which returns [TABLE="width: 1215"]
<TBODY>[TR]
[TD]ERROR - CALCULATION ABORTED: Calculation error in measure 'Agent Historical'[Rank of Unproductive Pct]: A table of multiple values was supplied where a single value was expected. It appears to be correct, so I am befuddled.[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]


=COUNTROWS(FILTER(all('Agents'[Agent Name]), 'Agent Historical'[M_Pct Unprod] (Values('Agents'[Agent Name]))<'Agent Historical'[M_Pct Unprod] && 'Agents'[Agent Name]<>VALUES('Agents'[Agent Name])))+1

 

[butlerrbrian]

Turns out the second filter clause boogers it up, which introduces a new problem - a value over 100% ranking as #1.

 

[miguel.escobar]

When doing rankings, you better use RANKX() or you'll encounter BIG performance issues. The COUNTROWS() approach was used back in the days of Powerpivot v1 but I would never use that approach ever again.

 

[butlerrbrian]

I thought to use RANKX() first, but it did return the expected result. I wonder if I had the wrong syntax.

 

[miguel.escobar]

you mean that it did not return the expected results? in that case, let's try fixing it =) Can you upload a copy of your workbook or maybe replicate the model in a new workbook with dummy data?

 

[butlerrbrian]

A clear mind in the morning (and a bit of reading Rob Collie), and I got the syntax for the base measures. Now to stack the rankings into an overall.