Calculated Field in a Pivot Table

David_j78

New Member
Joined
Jun 18, 2021
Messages
12
Office Version
  1. 365
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?

1624030501724.png
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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)


1624185630583.png


1624186226408.png
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top