Forum Discussion

hobosapien's avatar
hobosapien
Helper I
1 year ago
Solved

RANKX returning incorrect values for every row

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

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

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

    • hobosapien's avatar
      hobosapien
      Helper I
      1 year ago

      Sample Data:

       

      NameAdherence %
      Aubriella Maddox0.317
      Aubriella Maddox0.111
      Aubriella Maddox0.111
      Connor Oliver0.938
      Connor Oliver0.978
      Connor Oliver0.857
      Luke Rosales0.871
      Luke Rosales0.847
      Luke Rosales0.867

       

      Expected Results:

       

      AgentAdherenceRank
      Connor Oliver0.921
      Luke Rosales0.862
      Aubriella Maddox0.183
      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.