Average 2 Columns Pivot Table

[Kobi Merrikin]

Hi All,

I have a pivot table aggregating quarterly data of number of employees at multiple sites. In order to analyse this, I need to know the average number of employees in each quarter to use in an injury rate calculation.
I am trying to use a calculated column in the pivot table but this just gives me the average of each quarter, I need to know the average of them all.

My issue is, if I display the data as an average, it gives me the average of each quarter, not the cumulative average.
In the below screenshot, I have the 2 columns and a standard formula of the figure I need to report (I have blacked out some information)

If I change these to an average (instead of a sum) I just get the average for each line, not the total:

Hope someone can help! Thanks

 

[RoryA]

I suggest you update your profile to show which version of Excel/Office you have, as it will affect the options available to you.

You will need PowerPivot to do this - do you have it available?

 

[Kobi Merrikin]

I suggest you update your profile to show which version of Excel/Office you have, as it will affect the options available to you.

You will need PowerPivot to do this - do you have it available?

HI thanks for your reply. Apologies I didn't know I can do that. I am using Office 365 2021.

I am working on behalf of a client who does not want Macros used so I don't think I could use Power Pivot?

 

[RoryA]

Power Pivot is not related to macros. What version is your client using?