Jobs resolved per agent per day last month

[Anonymous]

Hi,

I'm trying to work out how to calculate the average number of jobs my techs do each day but we only work weekdays. I want to display the result for last month on a card. 

I think I'm pretty close with this but I think it's grabbing the total jobs resolved by the team, not just for the previous month as the result is a number that's way too large. 

Jobs per Tech per Day - RRT =

VAR Jobs_Resolved = CALCULATE(SUM(PowerBI_RRT_RESULTS_WITH_DETAILS[Resolved by RRT]))

VAR Weekdays_Last_Month = 'Calendar'[Weekdays for the Month]

VAR Number_of_Techs = AVERAGEX(KEEPFILTERS(VALUES('PowerBI_RRT_RESULTS_WITH_DETAILS'[Resolve Date].[Day])),CALCULATE(DISTINCTCOUNT('PowerBI_RRT_RESULTS_WITH_DETAILS'[Agent ID]),PowerBI_RRT_RESULTS_WITH_DETAILS[Action Group]="RRT"))

RETURN Jobs_Resolved/Weekdays_Last_Month/Number_of_Techs

Can someone help me iron this out please?

[Anonymous · Accepted solution]

Hello Everyone, 

Thank you for responding to this and apologies for my slow response. 

Unfortunately Amit's solution did not work and I found myself out of time to attempt futher efforts to resolve my problem at the time. 

For those who are interested, to resolve my issue I ended up just making calculated columns on my calendar table so I could roll everything up in the one spot. To do this I made three columns:

This one calculated how many jobs my team resolved for the day

Jobs per Day - RRT = CALCULATE(SUM(PowerBI_RRT_RESULTS_WITH_DETAILS[Resolved by RRT]),PowerBI_RRT_RESULTS_WITH_DETAILS[Resolve Date]=EARLIER('Calendar'[Date].[Date]))

This one calculated how many agents I had working that day

Agents per Day - RRT = CALCULATE(DISTINCTCOUNT(PowerBI_RRT_RESULTS_WITH_DETAILS[Agent ID]),PowerBI_RRT_RESULTS_WITH_DETAILS[Action Group]="RRT",PowerBI_RRT_RESULTS_WITH_DETAILS[Resolve Date]=EARLIER('Calendar'[Date].[Date],1))

This one calculated how many jobs each agent resolved for the day on average

CpTpD - RRT = CALCULATE(SUM('Calendar'[Jobs per Day - RRT])/SUM('Calendar'[Agents per Day - RRT]),PowerBI_RRT_RESULTS_WITH_DETAILS[Resolve Date]=EARLIER('Calendar'[Date].[Date],1))

Then in my cards, I simply displayed the resulting figures as averages for the month to display the daily average.

Hope that helps someone else. It's not the most elegant solution but it works and that's enough. 

THanks again everyone!

Kind Regards,

[v-shex-msft]

Hi @Anonymous,

Can you please share some dummy data and expected results to test? It should help us to clarify your data structure and do test coding formula on it. How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , change it like

in the original change this

RETURN divide(Jobs_Resolved,Weekdays_Last_Month*Number_of_Techs)

or try this

divide(CALCULATE(SUM(PowerBI_RRT_RESULTS_WITH_DETAILS[Resolved by RRT])),
Sumx(VALUES('PowerBI_RRT_RESULTS_WITH_DETAILS'[Resolve Date].[Date]),CALCULATE(DISTINCTCOUNT('PowerBI_RRT_RESULTS_WITH_DETAILS'[Agent ID]),PowerBI_RRT_RESULTS_WITH_DETAILS[Action Group]="RRT")))