Pivot Table - Column Headings not appearing when adding Calculated Field

[SuzieKD]

Hi All,

I'm very new to Pivot Tables and have come a cropper with what I'm working on now.

Can't seem to add a screen shot, so will do my best to explain...

I've got a table that summaries pension contributions across a whole financial year, pulling data from 12 separate worksheets (1 per month).

The rows are individual employees, and the columns are;

Pensionable Earnings
Employer Contribution
Member Contribution

My issue is that the Grand Total column is summing all 3 of the above columns, where I only want it to sum 'Employer Contribution' and 'Member Contribution'.

I've gone to add a Calculated Field to solve the problem, but it doesn't give me the column headings as field options to add to the formula, it just offers 'Row', 'Column' and 'Value'.

I'm aware that the real problem is my lack of knowledge about how to structure pivot tables!

Can anyone help?

 

[j_unsuitable]

Hey,

a few questions.
Do you have twelve pivot tables (one for each sheet) or did you consolidate the data into one sheet first?
With Pivot tables you need all your data in one sheet, so it would be best to simply add a column for "Month" and have all your data in one sheet. The pivot table will then allow you to filter for specific months, to show developments over the year etc. You could also add a column for "Total contribution" to your raw data where you simply add up those two columns, you wont need a calculated field then.

Do you need the "Pensionable Earnings" column in your pivot? If not you could simply remove it from the column labels and the grand total should be fine.

How did you go about trying to add the calculated field? Pivot table tools > options > formulas > calculated field?
That should give you a pop up window looking something like this:

And there in the box for fields it only shows row, column and value for you?

Trying to recreate that error without any luck. In order for your to create the pivot table in the first place the correct fields wouldve to been displayed in the Field list, so they should be there as well.
Did you try refreshing the pivot table? (sometimes its the easiest things..)

Sorry this isnt too much help, but maybe it brings us closer to the problem.
Maybe you could upload a sample workbook where you delete any critical data and link it here. That would take away a lot of the guessing and make it easier to find the problem rather than trying to recreate it.

Julian