0

I have been trying to add a calculated field in the pivot table. The entire pivot table is created perfectly so far. The last column that I want to add id the calculated one. Unfortunately, so far I cannot even do it manually. However, if I just insert as a formula right next to the pivot table the below provided formula, then it works perfectly. 

=GETPIVOTDATA('Monthly $/SU Forecast',$A$15,'T-Lane','Athens to GREECE')-GETPIVOTDATA('Monthly $/SU Actual',$A$15,'T-Lane','Athens to GREECE')

When I type the below inside the macro then it says 'Unable to get the PivotFields property of the PivotTable class' for the line 

.PivotFields("Diff").Orientation = xlDataField

The entire part of the code is this :

.CalculatedFields.Add "Diff", "=GETPIVOTDATA('Monthly $/SU Forecast',$A$15,'T-Lane','Athens to GREECE')-GETPIVOTDATA('Monthly $/SU Actual',$A$15,'T-Lane','Athens to GREECE')"
.PivotFields("Diff").Orientation = xlDataField

My Pivot Table

enter image description here

Pericles Faliagas
  • 636
  • 9
  • 29

1 Answers1

2

Your calculated field formula is invalid, which is why you can't add it manually. As per the article at http://www.contextures.com/excel-pivot-table-calculated-field.html

  • Calculated field formulas cannot refer to the pivot table totals or subtotals
  • Calculated field formulas cannot refer to worksheet cells by address or by name.
  • Sum is the only function available for a calculated field.
  • Calculated fields are not available in an OLAP-based pivot table.

Here's a screeshot showing how to set up a calculated field:

enter image description here

...and here's the result:

enter image description here

Note that creating Calculated Fields in this manner has pretty much been made redundant, as in Excel 2013 and later you can use the Data Model to do this and a heck of a lot more (even if your version of Excel isn't one of the 'premium' SKUs that ship with the PowerPivot addin).

Here's an example showing that approach:

First, select your data source, turn it into an Excel Table, and then create a new PivotTable but this time check the box at the bottom of the dialog "Add this data to the Data Model":

enter image description here

When you click on the PivotTable this produces, you'll see that the PivotTable Fields dialog looks slightly different: It will show the Table name, and then under that table it will list each field:

enter image description here

Right-click on the Table name in that dialog, and then click the Add Measure option:

enter image description here

Give the Measure a name, and type the formula you want in the dialog box. (You don't actually have to type that 'Sum of' stuff...just type 'Forecast' and you'll see an autocomplete box come up where you can choose the field you want)

enter image description here

Bam: You've got a new calculated field in the PivotTable:

enter image description here

jeffreyweir
  • 4,668
  • 1
  • 16
  • 27
  • Thanks for the feedback ! Then , do you have any idea how it is possible to make calculation inside the pivot table between cells of the pivot table ?? – Pericles Faliagas Oct 18 '17 at 19:20
  • See my revised answer. – jeffreyweir Oct 18 '17 at 19:27
  • I will provide a screenshot of my pivot table so as to be more clear... what I am actually trying to do is add a new column to my pivot table which will print the result of the difference between column "Monthly Forecasted $/SU" and "Actual $/SU". So, I want to have a column with three rows with values 0,11-0,28=-0,17 | 0,33-0,35=-0,02 | 0,17-0,18=-0,01 . So, what I actually want is a column to print the difference between the values of cells of the pivot table.. do you think it is possible?? Thanks again for your time! – Pericles Faliagas Oct 18 '17 at 19:58
  • What version of Excel do you have? – jeffreyweir Oct 18 '17 at 20:30
  • Its excel 2016! – Pericles Faliagas Oct 18 '17 at 20:31
  • 1
    Okay. Note the last line in my answer above: Creating Calculated Fields in this manner has pretty much been made redundant, as in Excel 2013 and later you can use the Data Model to do this and a heck of a lot more. Give Google a spin, and also check out ExcelIsFun YouTube channel for a whole heap of examples on how to do this. – jeffreyweir Oct 18 '17 at 20:34
  • Ok thank you !! The only reason that I tried to find a solution with this type of code is that I actually have created the entire pivot table with Vba, and it is a part of a huge macro actually, so its kind of sad to get stuck in the last line when you simple have to add just a column :( nevertheless, thank you very much for the hints! – Pericles Faliagas Oct 18 '17 at 20:38
  • Cool. I'll try to find time today to amend my answer to show how to do this with the DataModel. But given I've already answered your question, can you please accept my answer? – jeffreyweir Oct 18 '17 at 20:44
  • I answered another question on SO some time back that slaved a Table to a PivotTable, and effectively let you do what you are trying to do. I'll see if I can find the link and post it here. – jeffreyweir Oct 18 '17 at 20:49
  • WHat SKU of Excel do you have? I.e. professional plus? Office 365? Something else? – jeffreyweir Oct 18 '17 at 20:51
  • ok great!! literally, the only thing I want is just a column which will print the results as I told you above! I can even provide to you my entire code... Maybe you could be able to find a solution.. The weird thing is that EVERYTHING runs great.. but I cannot add this column.. I can add it externally with a formula, next to the pivot table but I want to add it inside! – Pericles Faliagas Oct 18 '17 at 20:51
  • I have the professional / business version (I think) – Pericles Faliagas Oct 18 '17 at 20:53
  • 1
    Okay, then you can use PowerPivot to do what you want. Even without that SKU, you could still achieve this using the Data Model and Measures, which is like Calculated Fields on steroids. (See my answer at https://stackoverflow.com/questions/40614801/excel-data-model-without-using-powerpivot/45044988#45044988 that has links regarding non-PowerPivot use of the Data Model). But you can also do this without the Data Model, using my Slaved Table method I talk about at https://stackoverflow.com/questions/38859656/select-newest-record-and-create-new-table-of-unique-values-in-excel/38867978#38867978 – jeffreyweir Oct 18 '17 at 20:57
  • that is great!! I will try to implement it tomorrow and if something comes up I will ask you :) thank you again!!!! – Pericles Faliagas Oct 18 '17 at 21:33