Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 a monthly basis. Data extract is similar to last table below. Firstly I need to filter our indirect items and then have an average per month (the average in this example does not make a lot of sense as it is from a very limited data range). The data consolidates as:
Hours | ||||
Client | Jun | Jul | Aug | Grand Total |
1 | 2.5 | 2 | 4.5 | |
2 | 2 | 2.5 | 2 | 6.5 |
3 | 2 | 4.5 | 6.5 | |
4 | 0.5 | 2.25 | 2.5 | 5.25 |
5 | 1.75 | 2 | 2 | 5.75 |
6 | 6 | 6 | ||
7 | 2 | 2 | ||
Grand Total | 8.75 | 6.75 | 21 | 36.5 |
Based on this the averages should read (note number of customers takes into account those that may have been counted in prior months as their total hours have been carried forward).
Hours - Running Total | No of customers | Average Hours | |
Jun-19 | 8.75 | 5 | 1.75 |
Jul-19 | 15.5 | 5 | 3.10 |
Aug-19 | 36.5 | 7 | 5.21 |
Date | Item | Total Hours | Program | Customer ID |
11/06/2019 | Direct | 0.5 | X | 1 |
25/06/2019 | Direct | 1 | X | 1 |
25/06/2019 | Direct | 1 | X | 1 |
9/08/2019 | Indirect | 0.5 | X | 1 |
9/08/2019 | Direct | 1 | X | 1 |
9/08/2019 | Direct | 1 | X | 1 |
5/06/2019 | Direct | 1 | Y | 2 |
5/06/2019 | Direct | 1 | Y | 2 |
31/07/2019 | Direct | 0.5 | Y | 2 |
31/07/2019 | Direct | 1 | Y | 2 |
31/07/2019 | Direct | 1 | Y | 2 |
28/08/2019 | Direct | 0.5 | Y | 2 |
28/08/2019 | Direct | 0.5 | Y | 2 |
28/08/2019 | Direct | 0.5 | Y | 2 |
28/08/2019 | Direct | 0.5 | Y | 2 |
19/06/2019 | Direct | 1 | X | 3 |
19/06/2019 | Direct | 1 | X | 3 |
1/08/2019 | Direct | 1 | X | 3 |
1/08/2019 | Direct | 1 | X | 3 |
15/08/2019 | Direct | 0.5 | X | 3 |
16/08/2019 | Indirect | 0.5 | X | 3 |
21/08/2019 | Direct | 0.5 | X | 3 |
21/08/2019 | Direct | 0.5 | X | 3 |
21/08/2019 | Direct | 0.5 | X | 3 |
21/08/2019 | Direct | 0.5 | X | 3 |
18/06/2019 | Direct | 0.5 | Y | 4 |
3/07/2019 | Direct | 0.25 | Y | 4 |
10/07/2019 | Direct | 1 | Y | 4 |
10/07/2019 | Direct | 1 | Y | 4 |
9/08/2019 | Direct | 1.5 | Y | 4 |
9/08/2019 | Direct | 1 | Y | 4 |
5/06/2019 | Direct | 0.5 | X | 5 |
5/06/2019 | Direct | 0.5 | X | 5 |
12/06/2019 | Direct | 0.5 | X | 5 |
13/06/2019 | Direct | 0.25 | X | 5 |
3/07/2019 | Direct | 0.5 | X | 5 |
3/07/2019 | Direct | 0.5 | X | 5 |
3/07/2019 | Direct | 1 | X | 5 |
22/07/2019 | Indirect | 0.5 | X | 5 |
22/08/2019 | Direct | 0.5 | X | 5 |
22/08/2019 | Direct | 1 | X | 5 |
22/08/2019 | Indirect | 1 | X | 5 |
22/08/2019 | Direct | 0.5 | X | 5 |
25/08/2019 | Direct | 4 | X | 6 |
31/08/2019 | Direct | 2 | X | 7 |
15/08/2019 | Direct | 2 | X | 6 |
Thanks
Kate
Solved! Go to Solution.
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
Hi @Anonymous ,
Why don't you mark this as solved so that others may find it. You could then repost your latest question as a new question. I will probably be off line for a couple of days. This gives others a chance to jump in answer. If you decide not to, I will look at this when I get back on line.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi @Anonymous ,
We had to add a filter for aver[Program] changing the ALL() to ALLEXCEPT(). Here is the updated PBIX. Average
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
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 ALLEXCEPT( aver,aver[Program] ) -- Removes any other filters from Date allexcept aver[Program] ) return Hours ------------------------------------- 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 ALLEXCEPT( aver,aver[Program] ) -- Removes any other filters from Date allexcept aver([Program] ) return Counter
Proud to be a Super User!
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])
Proud to be a Super User!
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.
@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])
Proud to be a Super User!
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
Hi @Anonymous ,
aver is my table name. Here is my pbix. aver
Let me know if you have more questions,
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
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
Hi @Anonymous ,
Ran into a little trouble with the dates as you can see, but the totals are correct in the right column. Will try to straighten out the dates.
Nathaniel
Proud to be a Super User!
Hi @Anonymous
The dates are good. Not sure what you are trying to do next.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |