Calculating agent rankings

[ajdm2007]

Hello everyone, 

I’ve got a formula in my project that I made to figure out the ranking based on agent performance. It takes several metrics into account.

AgentRanking = 
VAR TotalProjects =
    COUNTROWS(FILTER(zendesk, zendesk[type] = "Project"))
VAR TotalTicketsSolved =
    COUNTROWS(FILTER(zendesk, zendesk[Status] = "Solved"))
VAR TotalTicketsSolvedHighPriority =
    CALCULATE(
        COUNTROWS(zendesk),
        zendesk[Status] = "Solved",
        zendesk[Priority] = "High"
    )
VAR TotalTicketsSolvedLowPriority =
    CALCULATE(
        COUNTROWS(zendesk),
        zendesk[Status] = "Solved",
        zendesk[Priority] = "Low"
    )
VAR TotalTicketsSolvedNormalPriority =
    CALCULATE(
        COUNTROWS(zendesk),
        zendesk[Status] = "Solved",
        zendesk[Priority] = "Normal"
    )
VAR TotalTicketsSolvedUrgentPriority =
    CALCULATE(
        COUNTROWS(zendesk),
        zendesk[Status] = "Solved",
        zendesk[Priority] = "Urgent"
    )
VAR TotalTicketsSolvedUncategorized =
    CALCULATE(
        COUNTROWS(zendesk),
        zendesk[Status] = "Solved",
        zendesk[Priority] = "Uncategorized"
    )
VAR AvgFullResolutionTime =
    AVERAGEX(
        FILTER(zendesk, zendesk[Status] = "Solved"),
        SWITCH(
            TRUE(),
            zendesk[Full resolution time brackets (Unsorted)] = "0-5 hrs", 3,
            zendesk[Full resolution time brackets (Unsorted)] = "1-7 days", 10,
            zendesk[Full resolution time brackets (Unsorted)] = "7-30 days", 18,
            zendesk[Full resolution time brackets (Unsorted)] = "> 30 days", 35,
            0
        )
    )
VAR AvgFirstReplyTime =
    AVERAGEX(
        FILTER(zendesk, zendesk[Status] = "Solved"),
        SWITCH(
            TRUE(),
            zendesk[First reply time brackets] = "0-1 hrs", 0.5,
            zendesk[First reply time brackets] = "1-8 hrs", 4.5,
            zendesk[First reply time brackets] = "8-24 hrs", 16,
            zendesk[First reply time brackets] = "> 30 days", 35,
            zendesk[First reply time brackets] = "No replies", 0,
            0
        )
    )
VAR AvgFirstResolutionTime =
    AVERAGEX(
        FILTER(zendesk, zendesk[Status] = "Solved"),
        SWITCH(
            TRUE(),
            zendesk[First resolution time brackets] = "0-5 hrs", 3,
            zendesk[First resolution time brackets] = "1-7 days", 10,
            zendesk[First resolution time brackets] = "7-30 days", 18,
            zendesk[First resolution time brackets] = "> 30 days", 35,
            0
        )
    )
VAR AvgSolvedTime =
    AVERAGEX(FILTER(zendesk, zendesk[Status] = "Solved"), zendesk[SolvedTime(Hrs)])
VAR SameDayResolvedPercentage =
    DIVIDE(
        CALCULATE(
            COUNTROWS(zendesk),
            zendesk[Status] = "Solved",
            zendesk[SameDayResolved] = 1
        ),
        COUNTROWS(FILTER(zendesk, zendesk[Status] = "Solved"))
    )
RETURN
    (VALUE(TotalTicketsSolved) * 0.12)
	+ (VALUE(TotalProjects)* 0.12)
    + (VALUE(TotalTicketsSolvedUrgentPriority) * 0.12)
	+ (VALUE(TotalTicketsSolvedHighPriority) * 0.10)
    + (VALUE(TotalTicketsSolvedNormalPriority) * 0.08)    
	+ (VALUE(TotalTicketsSolvedLowPriority) * 0.05)
    + (VALUE(TotalTicketsSolvedUncategorized) * 0.01)
    + (AvgFullResolutionTime * 0.12)
    + (AvgFirstReplyTime * 0.08)
    + (AvgFirstResolutionTime * 0.08)
    + (AvgSolvedTime * 0.07)
    + (SameDayResolvedPercentage * 0.05)

What do you think about it?

If you have any suggestions for a better way to do this, I’d love to hear them.

Here’s the visualization I’m using:

Thanks for your help!

[Anonymous · Accepted solution]

Hi @ajdm2007 

Agree with Wilson_

Your DAX formula for calculating agent ranking based on various performance metrics is well-structured and thoughtfully considers multiple factors.
One bit of advice I can think of is that if you have a large amount of data, consider using the SUMMARIZE function to filter the “dendesk” table first, pre-summarizing may reduce the duplication of calculations.
A sample of my DAX formula is as follows:

VAR TicketsSolvedSummary = SUMMARIZE(
FILTER(zendesk, zendesk[Status] = "Solved"),
zendesk[Priority],
"PriorityCount", COUNTROWS(zendesk)
)
VAR TotalTicketsSolvedHighPriority = CALCULATE(SUMX(TicketsSolvedSummary, IF(zendesk[Priority] = "High", [PriorityCount], 0)))
VAR TotalTicketsSolvedLowPriority = CALCULATE(SUMX(TicketsSolvedSummary, IF(zendesk[Priority] = "Low", [PriorityCount], 0)))
VAR TotalTicketsSolvedNormalPriority = CALCULATE(SUMX(TicketsSolvedSummary, IF(zendesk[Priority] = "Normal", [PriorityCount], 0)))
VAR TotalTicketsSolvedUrgentPriority = CALCULATE(SUMX(TicketsSolvedSummary, IF(zendesk[Priority] = "Urgent", [PriorityCount], 0)))
VAR TotalTicketsSolvedUncategorized = CALCULATE(SUMX(TicketsSolvedSummary, IF(zendesk[Priority] = "Uncategorized", [PriorityCount], 0)))

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

[ajdm2007]

Hello @Anonymous ,

That's the kind of answer I was looking for, thanks for that golden nugget.

Have a good one.

[Wilson_]

ajdm2007,

This sounds like a question for the business, not a technical question. 🙂

[ajdm2007]

Hello @Wilson_ ,

You’re right; that formula does reflect what the business is evaluating. I’m just looking at it from a technical angle—whether it’s well-structured or if there’s room for improvement.

Thanks a lot for taking the time to answer!

[Wilson_]

hah, sorry about that, I misunderstood what "this" was when you said "better way to do this". Glad you were able to get a helpful response. 😄

(I defaulted to accountant brain it seems! 😂)