Averages in subtotals/totals instead of sum

[Anonymous]

Hi Experts

I've been struggling with the following problem. I have a pivot table where I calculate sum of FTE for employees with months in columns and department/employee_id in rows. Instead of sums in subtotals and totals, I want to see the averages. That's what I have right now in PBI - default aggregation with sum:

This is my desired result. I want average aggregation for subtotals/totals across both rows and columns, while keeping sum aggregation of FTE for each employee:

I have tried DAX formulas with AVERAGEX, but it allows for average aggregation only across single dimension.

[jgeddes · Accepted solution]

I created a dataset

Wrote the following measure...

Measure = 
var _vTable =
SUMMARIZE(
    emptable,
    emptable[Employee],
    emptable[Date].[Year],
    emptable[Date].[Month],
    "_val", SUM(emptable[Value])
)
var _result =
AVERAGEX(_vTable, [_val])
Return
_result

Where you create a summary table by employee and date and then take the averages of that table.

(It will depend on how your dates are set up. If you had a combined Year/Month column you should only have to add that column to summarize by.)

The result...

Hope this gets you pointed in the right direction.