Forum Discussion

PatrickWork2021's avatar
PatrickWork2021
Frequent Visitor
2 years ago

Help with average formula

Hi I have some data relating to tickets closed. This data contains, owner ID, closeddate and location, what I am trying to calculate is the average daily closures per agent so far on that data set (...
Need Help
bhanu_gautam's avatar
bhanu_gautam
Super User
2 years ago

PatrickWork2021 , Create a new measure 

 

DistinctNamesPerDayByLocation =
SUMMARIZE(
Closed,
Closed[ClosedDate],
Closed[Location],
"Distinct Names", DISTINCTCOUNT(Closed[OwnerId])
)

 

And one more

AverageDistinctNamesPerDayByLocation =
AVERAGEX(
DISTINCT(Closed[Location]),
CALCULATE(
AVERAGE(DistinctNamesPerDayByLocation[Distinct Names])
)
)

 

And one for average day closure

DYAveragePerDayByLocation =
DIVIDE(
DIVIDE(
Closed[Closed_Count],
[Days Elapsed]
),
[AverageDistinctNamesPerDayByLocation]
)

  • PatrickWork2021's avatar
    PatrickWork2021
    Frequent Visitor
    2 years ago

    Thanks for the attempt, for the first measure did I need to click new table and then pop that into the formula bar?

     

    The numbers your calcs brought up do not feel correct. So for example, my average distinct names per day comes out at 80, but this is including days (weekends) where the count is very low due to tickets being closed by automated processes (we don't work weekends).  If I removed those days, the answer would be closer to 100, your number is coming out at 74. The distinct names per day per location is only 22, again this should be much higher. 

     

    To recap, I have the number I needed for the daily average ticktes closed per per person, but I need to update the unique agents count for only days where the count is over 10.