Show averages in subtotals/totals instead of sum for PBI pivot table

[dmitry-wbr]

Hello everyone!
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.
I will gladly appreciate any help with that one. Link for source file: https://docs.google.com/spreadsheets/d/1mWVGys7-PAUWvryvMyhyjPmmF7iSDNEE/edit?usp=sharing&ouid=10915... 

[tamerj1]

Hi @dmitry-wbr 

you can use

IF(

ISINSCOPE (Table[Department] ),

"your average calculation",

"your sum calculation"

)

Also HASONEVALUE shall retrun averages for both subtotals ans the grand total

[dmitry-wbr]

Hi @tamerj1 
Thanks for the advice, but it doesn't seem to work 😞

[tamerj1]

The HASONEVALUE you use it with the sub-colum (E45, M12 .....)

IF ( HASONEVALUE (column name), SUM, AVERAGE)

[dmitry-wbr]

Can't reach the desired result unfortunately, values are not correct

[tamerj1]

This should work

Measure2 =
IF (
    HASONEVALUE ( '12'[Employee_ID] ),
    SUM ( '12'[FTE] ),
    CALCULATE ( AVERAGE ( '12'[FTE] ), ALLSELECTED () )
)

[dmitry-wbr]

That produces the same incorrect result unfortunately 

[tamerj1 · Accepted solution]

Hi @dmitry-wbr 
I think you are trying to calculate the average of the sum.
For each "Department" AVERAGE will calculate the sum of all values of all records of each employee and aggregate the total value for all employees. Then it divides this aggregated value over the total number of records. 
What you actually want is to find the average of the visible values in your matix which is the sum of these values divided by the number of these values. 
This shall not be a problem but the thing that makes it complicated is that you have another filter on the matrix column which is the Year-Month. Therefore more conditions shall apply

Measure2 =
VAR NumberOfEmployees =
    COUNTROWS ( VALUES ( '12'[Employee_ID] ) )
VAR NumberOfMonths =
    COUNTROWS ( VALUES ( 'Date'[Year-Month] ) )
VAR SumValue =
    SUM ( '12'[FTE] )
VAR Result =
    SWITCH (
        TRUE,
        HASONEVALUE ( '12'[Employee_ID] ) && HASONEVALUE ( 'Date'[Year-Month] ), SumValue,
        HASONEVALUE ( '12'[Employee_ID] ) && NOT HASONEVALUE ( 'Date'[Year-Month] ), DIVIDE ( SumValue, NumberOfMonths ),
        DIVIDE ( SumValue, NumberOfEmployees )
    )
RETURN
    Result

Please try and let me know

[dmitry-wbr]

Wow, that worked perfectly! Thank you a lot!

[tamerj1]

@dmitry-wbr that's great. Please let me know if you need any further help

[tamerj1]

@dmitry-wbr Just need to mention that you need to use the correct table name and column name of  "Year-Month"