Change grand total of pivot table to average, but keep the data itself as a sum - is this possible?

[MagicMoogle]

Hi there,

Using Office 365.

I am trying to pivot the FTE (full time equivalency, basically headcount) of a team by month and by grade, except I want the "Grand total" column to average all the months in the row. Currently, with the values summarised as sum, this is how the pivot looks:

What I would like, is for it to be like this:

But when I right click >Summarise Values By > Average it averages all the data, not just the grand total:

I have tried everything I can think of including adding in the FTE values field twice and setting one of them to sum and one of them to average, but of course that doesn't work because its still trying to average the average and producing things like 0.94 rather than 29.50 (the desired figure I want in J6).

This is part of an automation project for regular monthly reporting, and since the team could potentially add in new grades as well as new columns for the remaining months of the year, the solution must be a pivot table or something just as dynamic, not something static like a series of SUMIFs then an AVERAGE function.

Any help that can be provided would be greatly appreciated. Many thanks.

 

[RoryA]

You can do it if you use Power Pivot and the data model.

 

[Sam_D_Ben]

This is part of an automation project for regular monthly reporting

Also - If this is part of an automation project, set up the pivot table adjustments to update automatically whenever new data is added

 

[MagicMoogle]

You can do it if you use Power Pivot and the data model.

Thank you I will look into this. Much appreciated.

 

[MagicMoogle]

Also - If this is part of an automation project, set up the pivot table adjustments to update automatically whenever new data is added

Great idea - thanks for the suggestion.