Pivot Table: How to use summary fields in calculation

[YesConsulting]

What seems to me to be a simple, or obvious requirement:

I have a table summarized by an Amount field by year.

I need to be able to reference the Sub Total Year field in a calculation.
Not in reference to Grand Total...
but each years' total.

So, for example, get a percentage of year over year.

See the images for the Pivot table definition and the results.

The only way I have been able to deal with this (I won't say "solve") is to copy the values to a differnt column (M-O in this example) , and build the formula based on those copied fields. (Q-R)
Of course this breaks the model, for any expanding / collapsing, and when the data changes.... (lets say a new year's transactions)

I must be missing something, have no idea what it is!

Help?

PS. Using Excel 2019 (office 365)
----------------------
Just to say, I have not been a slacker on this... I have tried everything I know, spent hours looking &* researching...
I am prepared to be "humiliated" for missing something obvious...

This requirement seems so basic that I cant belive there are not loads of articles discussing it.
Even if it is impossible to do I would have expected to find this a general need?

I have poured over the Excel book by MrExcel!
I have spent hours trying to imagine how to form the search to get to the answer,
If there is any useful info at all, it is only to reference adding a calculated field and reference the field names that are being summarized in the source data.

Last resort....

 

[Worf]

The GETPIVOTDATA function maybe of use:

 

[YesConsulting]

The GETPIVOTDATA function maybe of use:

View attachment 2645

Here is what I learned about GetPivotData
It is a function that is optional, and set on the Pivot Table Options tab.
When it is on, references made in the table have the "GetPivotTable( " reference. Because this is keyed to a specific cell any formula that uses it cannot be Duplicated / Filled.

So turning this off allows "normal" handling/referencing of the cells in the Pivot Table outside the bounds of the Pivot table.

This works great, until you change the shape of the table by expanding/collapsing from the original Col/Row reference.
The formula outside the pivot table does not change its references automatically.

----

This solves a certain part of certain aspects of the problem... It is incomplete IMO.

I have some other ways to form the question now... I'll do some testing first.

--Thanks for the nudge... it got me 1/2 way there...

 

[YesConsulting]

What seems to me to be a simple, or obvious requirement:

I have a table summarized by an Amount field by year.

I need to be able to reference the Sub Total Year field in a calculation.
Not in reference to Grand Total...
but each years' total.

So, for example, get a percentage of year over year.

See the images for the Pivot table definition and the results.

The only way I have been able to deal with this (I won't say "solve") is to copy the values to a differnt column (M-O in this example) , and build the formula based on those copied fields. (Q-R)
Of course this breaks the model, for any expanding / collapsing, and when the data changes.... (lets say a new year's transactions)

I must be missing something, have no idea what it is!

Help?

It turns out there is no way to address the summary fields.
There are no tricks to do so either.
Thus there is no way to have a calculated field within the pivot table that can compute a percentage based on the pivot table selections and levels of expand/compress

Using the trick below you can make reference to a column that the data occupies in a pivot table
outside the table... But if the table structure is modified by expanding/compressing or later adding data that changes the form of the table...
for example if adding a new month of data that creates a new column.
the cell references won't update to follow the table changes... and will have to be reassigned.

I suspect there is some amazing vba trick, using the data model or something that could accomplish this function.
IF so it is a very well hidden secret!

 

[Worf]

From the site below:

A conventional Pivot Table does not allow one to have custom formulas in the Subtotal columns. I have been able to resolve this problem by using the free Power Pivot add-in from Microsoft for Excel 2010 and higher versions.

Perform different calculations in the Subtotal/Grand Total column of a Pivot Table