topic Re: Show averages in subtotals/totals instead of sum for PBI pivot table in DAX Commands and Tips

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

dmitry-wbr — Wed, 16 Feb 2022 16:13:24 GMT

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=109155426970323250863&rtpof=true&sd=true

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

tamerj1 — Wed, 16 Feb 2022 17:18:21 GMT

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

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

dmitry-wbr — Wed, 16 Feb 2022 17:30:35 GMT

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

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

tamerj1 — Wed, 16 Feb 2022 17:43:56 GMT

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

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

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

dmitry-wbr — Thu, 17 Feb 2022 06:47:13 GMT

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

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

tamerj1 — Thu, 17 Feb 2022 07:36:07 GMT

This should work

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

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

dmitry-wbr — Thu, 17 Feb 2022 08:36:52 GMT

That produces the same incorrect result unfortunately

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

tamerj1 — Thu, 17 Feb 2022 12:05:17 GMT

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

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

tamerj1 — Thu, 17 Feb 2022 12:15:44 GMT

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

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

dmitry-wbr — Thu, 17 Feb 2022 14:46:01 GMT

Wow, that worked perfectly! Thank you a lot!

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

tamerj1 — Thu, 17 Feb 2022 16:23:06 GMT

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