How to Average Totals in the Pivot Table

Hi there,

Is there a way to make a pivot table include a Grand Total average? I want to average the totals by fiscal year not average each of the individual entries amounts. The cut and paste of the table is below, along with a handmade column of averages that I want. But I want to be able to include the Average column in pivot charts. I've attached an image here.


Capture.PNG


Sum of Amount Column Labels
Row Labels 2016 2017 Grand Total FY Average
Costumes $441,566.35 $513,879.94 $955,446.29 $477,723.15
IATSE Carpenters $92,132.37 $196,114.04 $288,246.41 $144,123.21
IATSE Stagehands $619,472.62 $669,747.54 $1,289,220.16 $644,610.08
Lights $105,502.22 $140,349.87 $245,852.09 $122,926.05
Production Education $41,659.46 $48,703.84 $90,363.30 $45,181.65
Production Office $150,693.40 $272,037.22 $422,730.62 $211,365.31
Properties $252,055.30 $278,729.47 $530,784.77 $265,392.39
Scenery $454,097.27 $429,579.64 $883,676.91 $441,838.46
Sound $90,959.56 $88,490.19 $179,449.75 $89,724.88
Stage Management $302,015.43 $40,714.66 $342,730.09 $171,365.05
Video/Projections $11,504.24 $16,493.13 $27,997.37 $13,998.69
Grand Total $2,561,658.22 $2,694,839.54 $5,256,497.76 $2,628,248.88

So, my trouble is that when I just change the value calculation to average, it does not average the yearly totals together, it averages all of the individual numbers together. I want the average over the years,not the average per account if that makes sense?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Okay, I think I've been able to attach that. Thank you!

There is no before sheet, there is just a pivot table as the before and the mocked up manual column next to it with what I want to achieve through the pivot table

The source data is in the mockup document as well.

Is that what you want?

Done with PowerQuery and Calculated Field (PivotTable)

No worries, I realized when you replied that that may have been confusing.

In PowerQuery FY is separated to 2016 / 2017 so I can AVERAGE them (Calculated Filed) in PivotTable

to see it : Data - Show Queries - dbl click on table from the right side and you will see transformed table

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it already - ignore it.
Thank you.

As we continue to update this data, will the pivot table update itself or is that something I'll have to redo when we update our data?

You can change/add/remove anything in your source table then right click on PivotTable and select Refresh

Now, I'm trying to connect a Slicer from those pivot tables to one I initially made with a Fiscal Year field. I'm not sure how to connect them or how to get/keep the FY/amounts field with all of the queries/averaging stuff that happened since the queries from the original pivot table separated the fields into their years/amounts to average them.

Sample attached.

You can connect these three PTs with one slicer if there is any common field for all three.
Create slicer - right click onis slicer - Report Connection - select Pivots - Ok
but I see you did it....

I was able to connect two of them with the slicer, but couldn't figure out how to get the spending by fiscal year one to connect with the shared fields.

You've FY in one PT only so you can not connect the other two
if you create somehow FY in these PTs you will be able to do it. You do not need to add FY to any area but FY must exist in all three

If you create all charts (Pivots) from the same query table you can use single FY slicer
I did FY for two PTs and single slicer

so far so good, but... top left chart has another source with FY...

probably you'll need re-create all from the begining... it will be easier than lookin' "how to.." and waste time.

Great. If I have to go back to the beginning and redo them, I'm confused as to how I would be able to keep the query you created to average those details without losing the FY field to connect later?

You can delete everything from the sheet (charts and pivots) then re-create it from the same Query table but with different fields
remeber, work on copy not on original file

Right, but if I delete all of those, then I am still at a loss as to how to recreate that query and those fields?

I'm so sorry about this. I feel like I must be missing something key here and am just not understanding.

deleting content of sheet doesn't delete query table

try from the begining... (don't delete anything)
on new sheet select:
Insert - Pivot Table then

pqpt.jpg

then do that again for the next PT and again for the next

with each PT you can create Chart and after all create slicer