Average of aggregate (sum) in Pivot Table

Question

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]

Answer 1

@BD2009
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.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.