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 (it started on the 31st March 2024) but also to be able to show by location.

 

Measures in place:

 

Count of tickets closed, days elapsed and using AI to help a further measure (created by adding a new table) to show me the average daily count of agents. 

 

So now I have an answer to my question which I am happy with, my challenge is I cannot then have a table showing this by location. So lets say the answer is 20 per day overall, if I drop the location field in, it will show me 10 (loc 1), 5 (loc 2), 5 (loc 3) which is incorrect, the value will be something like 20 per location. 

 

The measures created in the new table are:

 

Average Distinct Names Per Day = AVERAGE(DistinctNamesPerDay[Distinct Names])
Distinctnamesperday = SUMMARIZE(Closed,Closed[ClosedDate],"Distinct Names",DISTINCTCOUNT(Closed[OwnerId]))
 
The one below then gives me overall answer of tickets closed on average per day per person. 
DY Average Per Day = DIVIDE(DIVIDE(Closed[Closed_Count],[Days Elapsed]),[Average Distinct Names Per Day])
 
I was using AI to help me yesterday and making progress but starting fresh today it's kind of lost what I have already done.
 
I should also add, that I would also like to update the calculation I have in terms of the average number of agents closing tickets each day because currently it is showing weekends where there are tickets being closed but they should not be factored in to the average as they are automated processes. So I can set a limit here where if the agent count is below 10 then we dont need to count.
 
Help!
Need Help

3 Replies

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi PatrickWork2021 

     

    Please provide sample data that fully covers your issue(in the form of table or pbix file) and the expected outcome based on the sample data you provided. So that I can help you better. Please remove any sensitive data in advance.

     

     

     

     

    Best Regards,

    Jayleny