Calculated Field in a Pivot Table

David_j78

New Member
Joined
Jun 18, 2021
Messages
3
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,704
Office Version
  1. 365
Platform
  1. Windows
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
 

David_j78

New Member
Joined
Jun 18, 2021
Messages
3
Office Version
  1. 365
I tried using "calculated item", but it give me the below error.

1624289229096.png
 

David_j78

New Member
Joined
Jun 18, 2021
Messages
3
Office Version
  1. 365
I have tried to ungroup as well, but it won't seem to let me.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,704
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,400
Messages
5,706,217
Members
421,433
Latest member
yash0468

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
Top