Daily average per working day calculation in power pivot

Question

I need to create a daily average measure for resolved complaints for each staff member, but am having a difficult time. I have a table with a list of complaints resolved, a calendar table with a working days indicator, and a staff list.

My output should be a one row table that averages the number of complaints closed per working day for today. It will update the average each day as the data is refreshed.

I created a measure that counts the number of closed complaints, [Count of Closed Complaints]. I also created a measure that calculates the elapsed number of working days, [Elapsed Working Days] which is =Day(Today())-TOTALMTD(calculate(count(Calendar[work hours]), Calendar[Work Hours]=0), Calendar[DateKey])

These two measures appear to work correctly.

However, the daily changing average is driving me nuts. I have tried the following formulas (as well as many others that use TOTALMTD) and cannot get it to work:

test average 4 :=CALCULATE(([Count of Closed Complaints]), FILTER(ALLSELECTED(calendar), calendar[Datekey] <= max(calendar[datekey])))/[Elapsed Working Days]

test average 3 :=calculate(([Count of Closed Complaints]/[Elapsed Working Days]), calendar[work hours]>0, DATESMTD(Calendar[DateKey]))

test average 2 :=calculate(AVERAGEA([Count of Closed Complaints]), DATESMTD(Calendar[DateKey]), calendar[work hours]>0)

Please help. Thank you!

Answer 1

I think you mean you want to count closed complaints, not to average them. Use the DISTINCTCOUNT function.

Answer 2

I may have misunderstood what you are trying to do but it seems as though you are overcomplicating things.

Your average is simply your [Count of Closed Complaints] divided by [Elapsed Working days]. Consider a measure as follows:

Closed per day :=
[Count of Closed Complaints] / [Elapsed Working Days]

This measure should return the Number of Closed Complaints per working day for all contexts that you want. So if you filter on one staff member for one month you will get the Number of Closed Complaints per day for that staff member for that month.

To make this work you will need to ensure that your Calendar table contains one record per day up to today. That will mean that your average for the current month will always be correct. Your Elapsed Working Days measure can be simplified to

Elapsed Working Days :=
CALCULATE (
    COUNT ( Calendar[work hours] ),
    Calendar[Work Hours] <> 0
)

Answer 3

Thanks for the help. My calendar runs until 2025...

I think I may have resolved my problem that gives me the correct answer today. I'll check on Monday to see if it still works!

I had to change my formula for Elapsed Working Days to: =Day(Today())-TOTALMTD(calculate(count(Calendar[DateKey]), Calendar[Work Hours]=0), Calendar[DateKey])

Then, I created the simple average of [Count of Closed Complaints] divided by [Elapsed Working days] but wrapped it in TOTALMTD. So, TOTALMTD([Count of Closed Complaints]/[Elapsed Working Days], Calendar[DateKey])

Displaying only today in the pivot table gives me the correct average for today. As I said earlier, I will see what happens this weekend and on Monday. I thought I'd solved this problem earlier and then the next day it wasn't.