Pivot table cannot create calculated item

I've encountered a problem with pivot tables in Excel.

I want to create calculated item, but it does not allow me, as it says This Pivot report field is grouped

But it's not grouped in any way.

I even tried to copy my data to new workbook. It contains only one names in rows, months in columns and one single value as value. I've never grouped anything, but it is still consider this table as grouped.

Does anyone know, why that could be? Thanks!

2 Answers

Try this:

On the actual pivot table, right click the columns months, select Ungroup

Let me know if this does not work and I will remove this answer.

It's possible to create Calculated Field for Grouped Pivot Table.

With attached Screen Shot you can find everything,

• Source Data.
• Pivot Table(Grouped) without Calculated field.
• PT with Calculated Field.
• Dialog to Insert Calculated Field.

Note,

• It's needless to illustrate the required steps since you are aware with the process.
• Alter the equation for Calculated Field and Source Data Range as your need.

Edited:

This portion is showing that how can you generate Calculated Field for Groped Items.

In below shown Screen Shot you can find Calculated Field Sum Of Grouped Items in red color.

Also check the Insert Calculated Item dialog for the Calculate field.

How it Works:

• Select anywhere in PT & from PT ANALYZE Tab find & hit Fields, Items & Sets.
• Next, Select Calculated Items
• At the Insert Calculated Items Dialog, in the Formula Text box select any Grouped Field/Fields to compose the required Formula.

N.B.

• Check the Screen Shot I've used 2 Calculated items ABC & DEF.
• If still you find any issue write in Comments I'll extend the help.