Calculated Field in a Pivot Table

[David_j78]

I am trying to set up a calculated field in a pivot table, but not sure if it can be done in it's current set up.

Below is how the pivot table I was given to work with is set up:

What I am trying to calculate is a variance column that show "week ending 6/12 CY - PY / CY" to get Var % change.
But I need to show this by line item (i.e. Body Shop change CY vs PY, Dealership CY vs PY, etc, etc).
I also need to do this for week ending 6/5 CY vs PY, and then week ending 5/29 CY vs PY.

As you can see in the fields, all i have to select is "subchnl_dsc" where Body Shop, Dealership, etc falls under. So I don't know how I can write a formula that will allow me to insert a calculated field. Let alone do it for multiple column with the same headings.

Thoughts on how to add this variance calculation in the pivot table?

 

[Alex Blakenburg]

I am pretty sure that the issue is, that you are thinking calculated field when in fact you want calculated item.
If you are comparing what on the data sheet are separate columns you use calculated fields ( a field equates to a column )
In your case "version" is held in a single column (which is in fact the ideal setup) which means that they are items not fields.
If you click in any values heading and go to PivotTable Analyze > Fields, Items & Sets > Calculated item.
(if you click on the word "version" or CY or PY - "version" will already be highlighted in the Fields list on the 2nd screen below)

 

[David_j78]

I tried using "calculated item", but it give me the below error.

 

[David_j78]

I have tried to ungroup as well, but it won't seem to let me.

 

[Alex Blakenburg]

Do you have multiple Pivots off the same data?
If so they will be sharing the pivot cache and grouping is at the cache level. Try selecting a subset of your data in that pivot (backup your spreadsheet first) and see if you can ungroup then.
I am guessing the grouping is on the date field.
Once you ungrounded you can reinstate the full data range on that pivot (it should now be using a different cache)

You may need to consider a helper WE field on the data sheet to get the grouping back or using the Data Model & Power Pivot for the grouping.