Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Average Calculations

Hi   I need to calculate an average hours spent with customers on a monthly basis per program. What I am trying to determine is whether the average time spent with a customer is going up or down on...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Thanks, that has worked well other than I have ran into an issue. I need to be able to filter by program and get the average hours on the program. I have tried to do this with a slicer. It changes everything bar the cumulative count of clients, which then impacts the average hours. What can I put in the measure to be able to take into account of this?

     

    For example Customers 1-4 maybe in Program A and 5-7 maybe in Program B and so on.

     

    Thanks

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

Hi Anonymous ,

So is this the table you would like to produce? Below you will find the measures.

Let me know if you have any questions.

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

 

 

 

Count of customers = Countrows(DISTINCT(aver[Customer ID]))
Total Hours measure = SUM(aver[Total Hours])
Average Hours per Customer = DIVIDE([Total Hours measure],[Count of customers])
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Nathaniel

     

    Thanks for your solution, that has worked. What I need to do next is on a monthly basis get the running average. So for example, June is as per the table. July and August would like below:

     

    Total Cumulative Hours

    Customer Count

    Average Hrs

    July

    15.5

    5 (this includes the 2 in June which were not in July

    =15.5/5=3.1

    August

    36.5

    7

    =36.5/7=5.21

     

    I am easily able to calculate the cumulative hours and average hrs, what I am having issues with is the customer count as I need to take into consideration if the customer has already been a prior customer even if there has been no activity in the current month. In essence what I am trying to do is get the total row as cumulative columns.

    • Nathaniel_C's avatar
      Nathaniel_C
      Community Champion
      6 years ago

      Anonymous ,

      Thank you, that makes sense! One thought springs to mind is what about customers who leave the practice?

       

      Let me know if you have any questions.

       

      If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
      Nathaniel

       

      Cumulative Count of Customers = 
VAR MaxDate = MAX ( aver[Date] )-- Saves the last visible date
var Counter =
    CALCULATE (
        [Count of customers],            -- Computes customers
        aver[Date]<= MaxDate,   -- Where date is before the last visible date
        ALL ( aver )               -- Removes any other filters from Date

   )
return Counter

Total Cumulative hours = 
VAR MaxDate = MAX ( aver[Date] )-- Saves the last visible date
var Hours =
    CALCULATE (
        [Total Hours measure],            -- Computes hours
        aver[Date]<= MaxDate,   -- Where date is before the last visible date
        ALL ( aver )               -- Removes any other filters from Date

   )
return Hours

These two are almost the same, and finally...

Total Average Hours = Divide([Total Cumulative hours],[Cumulative Count of Customers])

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Nathaniel

         

        How do I get the aver[Date]? Once I type the ( after Max it wants to pick up a field from the table, if I type aver in prior I cannot pick up the date field.

         

        Thanks

        Kate