 # Pivot table - average the total row

• Hi, I have a pivot where the daily rows need to be summed, however over the month summing doesn't make sense, I'd like the average over the month displayed.

Data Table

 Date Group Number Fri 1 Jul 22 Group A 10 Fri 1 Jul 22 Group B 15 Sat 2 Jul 22 Group A 5 Sat 2 Jul 22 Group B 10

Resulting Pivot table The daily rows are correct, summing Groups A & B, however I'd like the Total row to be an Average of the daily figures, so in this case I'd like it to show 20.

• Do you have a version of Excel with Power Pivot?

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

• Here's an example using Power Pivot. There are two measures involved:

TotalNumber which is defined as =SUM(Table1[Number])

and

TotalWithAverage which is defined as =if(HASONEVALUE(Table1[Date]),[TotalNumber],averagex(values(Table1[Date]),[TotalNumber]))

## Files

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!