RANKX returning incorrect values for every row

[hobosapien]

I have put together the following code which returns the first table below

EVALUATE

        VAR Table1 = SUMMARIZE('f_Call Metrics','f_Call Metrics'[Agent],"AVG Adherence",AVERAGE('f_Call Metrics'[Adherence %]))

RETURN

    Table1

When I attempt to add a ranking by adding the second line with RANKX it returns a value for each row but they're all much higher than the total number of rows in the table (55 rows total). Where did I go wrong?

EVALUATE

        VAR Table1 = SUMMARIZE('f_Call Metrics','f_Call Metrics'[Agent],"AVG Adherence",AVERAGE('f_Call Metrics'[Adherence %]))

        VAR TableRanking = ADDCOLUMNS(Table1,"Rank",RANKX(All('f_Call Metrics'),CALCULATE(AVERAGE('f_Call Metrics'[Adherence %])),,DESC,Dense))

RETURN

    TableRanking

[ryan_mayu]

could you pls provide some sample data (not the screenshot) and the expected output?

[hobosapien]

Sample Data:

Name	Adherence %
Aubriella Maddox	0.317
Aubriella Maddox	0.111
Aubriella Maddox	0.111
Connor Oliver	0.938
Connor Oliver	0.978
Connor Oliver	0.857
Luke Rosales	0.871
Luke Rosales	0.847
Luke Rosales	0.867

Expected Results:

Agent	Adherence	Rank
Connor Oliver	0.92	1
Luke Rosales	0.86	2
Aubriella Maddox	0.18	3

[Anonymous · Accepted solution]

Hi @hobosapien ,

I suggest you to try code as below.

EVALUATE
        VAR Table1 = SUMMARIZE('f_Call Metrics','f_Call Metrics'[Name],"AVG Adherence",AVERAGE('f_Call Metrics'[Adherence %]))
        VAR TableRanking = ADDCOLUMNS(Table1,"Rank",
		RANKX(Table1,CALCULATE(SUM('f_Call Metrics'[Adherence %]),FILTER(Table1,'f_Call Metrics'[Name] = EARLIER([Name]))),,DESC,Dense))		
RETURN
   TableRanking
ORDER BY [Rank]

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hobosapien]

Your solution works but I found I was not referencing the earlier Summarized table in my RANKX call and that fixed it. Thank you for the help!

[ryan_mayu]

@hobosapien 

pls see if this is what you want