How to calculate average of monthly averages?

[Sachintha]

I have a data set of OT hours worked by employees, which looks like below.

Date,Name,Hours
1/15/2024,Jason,5
1/16/2024,Chelsey,1
1/17/2024,Mika,2
1/18/2024,Jason,3
1/19/2024,Chelsey,4
2/1/2024,Chelsey,3
2/2/2024,Chelsey,4
2/3/2024,Jason,4
2/20/2024,Mika,5
2/21/2024,Tiffany,3
2/22/2024,Jason,4
2/23/2024,Jason,2
2/24/2024,Tiffany,3
2/25/2024,Chelsey,4
3/26/2024,Mika,2
3/27/2024,Chelsey,3
3/28/2024,Chelsey,4
3/29/2024,Tiffany,5
3/30/2024,Mika,3
3/31/2024,Tiffany,4

As you can see in the sample data set, not all employees will work OT hours every month, and one employee can do OT multiple days a month.

Now, I need to show this in a Matrix visual, with monthly averages of OT. In other words, for every month, I need to show the total number of OT hours peformed that month divided by total number of unique employees who did OT that month. The keyword here is 'unique' employees. Which is, if we take Januray as en example, total number of OT hours is 15, and the total number of unique employees who did OT was 3 (Jason, Chelsey, and Mika). I do NOT want to divide by the instances OT performed. So, for the month of January average should be 5.00. The Matrix would look like this:

To achieve this, I created a bunch of measures:

OTHours = SUM(OTHOURS[Hours])

UniqueEmps = DISTINCTCOUNT(OTHOURS[Name])

OTAvg = DIVIDE(OTHOURS[OTHours], [UniqueEmps])

This is fine, and works as expected.

However, I have an additional task. In the same Matrix, I want to show the 'Average of Averages' for the year, based on the number of months in the selection (in this case, only 3 months). What I mean by this is that, I want to add up 5.00, 8.00, and 7.00 above, and divide them by 3, so I get 20/3 = 6.67. But, if I enable 'Columns subtotals' in the Matrix, this is not what I get. It essentially sums up the total hours (which is fine), but then divides by unique employees for the entire year, which is 4. So, 68/4 = 17.00, as you can see below.

How to I get the Matrix to calculate the Average of Average instead of simply applying the 'OTAvg' measure to the entire data set?

[quantumudit]

The following DAX code should resolve your issue. Here is the code:

OT Hours Average = 
VAR _otHours =
    SUM ( OTHOURS[Hours] )
VAR _uniqueEmp =
    DISTINCTCOUNT ( OTHOURS[Name] )
VAR _otAvg =
    DIVIDE ( _otHours, _uniqueEmp )
VAR _months =
    DISTINCTCOUNT ( OTHOURS[Month] ) // This provides the unique months (selected in slicer) in year, so, for column total its 3 

// Sum over a virtual table that gives the total avg hours, i.e., 20 
VAR _totalOTAvg =
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE (
                OTHOURS,
                OTHOURS[Month],
                "emp", DISTINCTCOUNT ( OTHOURS[Name] ),
                "hrs", SUM ( OTHOURS[Hours] )
            ),
            "avg_hrs", DIVIDE ( [hrs], [emp] )
        ),
        [avg_hrs]
    )
VAR _yearAvg =
    DIVIDE ( _totalOTAvg, _months )
RETURN
    IF ( ISINSCOPE ( OTHOURS[Month] ), _otAvg, _yearAvg )

Well to get the months from the date, I created a calculated column with the following DAX (maybe you don't have to do it if you have a month column in your data):

Month = EOMONTH(OTHOURS[Date], 0)

Here is GIF of the solution at work:


Please leave a kudo if the solution resolves your issue. I would appreciate it. Thanks 😊

[jgeddes · Accepted solution]

You can try this measure...

Average of Average = 
var _vTable =
SUMMARIZE(
    OTHours,
    OTHours[Date].[Month],
    "__average", [OTAvg]
)
RETURN
AVERAGEX(
    _vTable,
    [__average]
)

[Sachintha]

Thank you! I like the simplicity here.