Excel 2007 Pivot Table Calculated Field


New Member
Feb 16, 2007
Hello to all,

I believe that I am finally getting the hang of pivot tables and VBA ... pretty nice. Now for my latest frustration - calculated fields.

I have a pivot table created which compares two years of data. The problem seems to be that this data is from the same data field (PINSAL) even though it shows in two columns (year 2007 and year 2008).

1 - I need to subtract the 2007 figure (column C) from the 2008 figure (column D) in a calculated field called DollarVariance
2 - I need to divide DollarVariance into the 2007 figure to create a calculated field called PercentVariance

This seems easy to do if I had two different variables used to create the 2007 and the 2008 data but it is the same datafield. Can I use column letter? Can I use the column name assigned by the pivot routine (12 - 2007 and 12 - 2008)? Suggestions?

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
You can add the value column as a data field again, then Show data as % Difference From Base field PINSAL Base item (previous).
Upvote 0
What is the VBA code for that? Here is what I have so far (which is unsuccessful)

 Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Worksheets(nmhld).Range("A1:" & rtcol & botrow))
    Set pvt = pvc.CreatePivotTable(TableDestination:=wks.Range("B4"), TableName:="SlsPIRES")
    'pvt.ManualUpdate = True
    With pvt
        .PivotFields("Period").Orientation = xlColumnField
        .PivotFields("Period").Position = 1
        .PivotFields("YEAR").Orientation = xlColumnField
        .PivotFields("YEAR").Position = 2
        .PivotFields("SEGMENT").Orientation = xlRowField
        .PivotFields("SEGMENT").Position = 1
        '.PivotFields("PISTE").Orientation = xlRowField
        '.PivotFields("PISTE").Position = 2
        .PivotFields("PINSAL").Orientation = xlDataField
        '.PivotFields("PINSAL").Position = 1
        .RowGrand = False
        .TableStyle2 = ""
        .RowAxisLayout xlTabularRow
        .CalculatedFields.Add "DollarVariance", "=[12 - 2008]-[12 - 2007]"
        .PivotFields("DollarVariance").Orientation = xlDataField
        '.PivotFields("DollarVariance").Position = 2
    End With
Upvote 0
Sorry, submitted a little too soon. The names in brackets are assigned column names by the pivot procedure. I will need to show both the dollar difference along with percent difference.

While we are at it, is there a way to hide the subtotal? RowGrand is hidden successfully but I want to hide the subtotal row also since the total of 2007 and 2008 values is useless.
Upvote 0
I would record a macro while doing it manually to get the code.
Upvote 0
Well, that was highly unsuccessful. If I drag PINSAL into the Values section I see three extra TOTAL columns and they have the same numbers in them. This makes sense so far. Then I change the Sum of PINSAL2 value field settings to Difference From (with PINSAL and (previous) selected) and those values change to #N/A. What am I missing???
Upvote 0
Sorry, I don't know what you are missing. It worked when I tried it in Excel 2000.
Upvote 0
No idea what was missing either other than the fact that this is 2007 and it works quite differently. I did get to the result that I wanted, although it is a workaround. I copied the main columns that I wanted, did a pastespecial as values in another area of the sheet, and then added the calculation columns to the right. That worked out perfectly and it eliminated the subtotals column that I could not find a way to remove.
Upvote 0
I have the similar issue with VBA PivotTable Calculated Field.
It works fine, but It would be great and I want to greate and use Name Range that would be used as part od Calculated Field.

My Problem is that even if I create Name Range in other sheet (because PivotTable with wanted Calculated Field is greated via VBA) and it should be in other sheet and PivotTable is.

The formula is basicly:

Date_MTD is problem, because it is Name Range in other sheet the pivot is and value is 90.
Error is: Run-time error 1004
Unable to get the PivotFields property of the Pivot Table class

Is it possible to get this to work, or is it possible to use name range in pivot table calculated field in this fashion?

Upvote 0

Forum statistics

Latest member
edris Alsatouf

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