question

BenDrake-1136 avatar image
0 Votes"
BenDrake-1136 asked emilyhua-msft commented

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]

office-excel-itpro
data.png (6.9 KiB)
pivot.png (37.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

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.

0 Votes 0 ·

I updated it to office-excel-itpro.

0 Votes 0 ·

1 Answer

emilyhua-msft avatar image
0 Votes"
emilyhua-msft answered emilyhua-msft commented

@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.
81639-capture58.png
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".
81720-capture59.png

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.


capture58.png (18.2 KiB)
capture59.png (82.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

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

@BenDrake-1136
Please check in to see if the information provided was helpful.
If you have questions, you may post back.

0 Votes 0 ·