# question

## Average of aggregate (sum) in Pivot Table

I'd like my pivot table to perform a subtotal calculating the average of the visible cells (i.e. not the underlying data) (e.g. average account sales per industry, average of the quarters within a year) I realize the answer is somewhere inside of a power pivot measure but I can't quite figure it out.

Any help would be much appreciated!

something like this (swap what the pivot table is doing for the manual calcs off to the side/below) ![79805-image.png][1]

My data is a flat sales transactions file that looks like this (simplified): ![79795-data.png][1]

data.png (6.9 KiB)
pivot.png (37.5 KiB)
· 2

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I realize the tag is quite incorrect, but there was not an available option for anything regarding pivot tables, or excel even... needs to be fixed.

BenDrake-1136

I updated it to office-excel-itpro.

@BenDrake-1136
Could you please explain that how did you get the value of "Avg of I1 accts" and "Avg of I2 accts"?
Will the number of occurrences of each account be counted?

If you want to achieve your needs via Pivot Table, we need to count the "Qtrs" numbers for every account and every year, like the following table (I got it via Power Query), but it is hard to create a relationship between this table and your source data.

More detailed information, please refer to "Relationships between tables in a Data Model" and "Create a relationship between tables in Excel".

I would suggest you handle your flat data via formulas or Power Query first.
Here is a similar sample I created as yours, and I added a column of "Year", such as "Y17", "Y18". Then I use formula to get the data that I want for every account and every Year, such as "SUM-Y17", "CountY17".

But if you need Column labels for year and quarter in pivot table, I suggest you create 2 pivot tables and put them together.

Any updates, you may share with us.