pivot table - unable to add calculated item - Excel says it's grouped but why?

[szilvia_vf]

Hi all,

I want to understand why Excel consideres a pivot table grouped and how this could be resolved?

So I have a table with 10 columns, and I want to make a pivot table where only 3 cols are affected. Highlighted with blue, cols D, G and H.

Here is my pivot:

- columns D and G as rows

- H as values

Now if I want to add a calculated field, say, the ratio of "víz" and "limonádé", it tells me that I can not do that because the Pivot Table report field is grouped.

Now if I simply copy-paste only these columns to a separate ranges and create the very same pivot table from this, Excel has no problems with creating the calculated item.

So why can I add calculated Item from the second, and why can't I add a calculated item from the first table? I obviously did not add a manual grouping, it is just putting multiple fields into the Rows.

Thank you for your explanation!

sample files attached:

sample - pivot item issue - 0.xlsx --> I created some pivot tables before this one, I deleted everything not needed. I tried to rebuild the cash, did not work.

sample - pivot item issue - success.xlsx --> I made a copy of the data sheet itself, and I wanted to make a pivot to reproduce the issue - and I failed... I can create a calculated item here. Hm...

[#2]

@szilvia_vf 

In general it's better to work with data model, but if with cached PivotTable calculated item could be added:

Perhaps you may share sample file?

[#3]

yes, I can do that when the dataset is just 3 columns wide. But I have 7 more columns as in my screenshot. What I can not understand: why is adding a calculated item possible when the source is just 3 columns wide, and why is it no longer possible when I have 7 more columns. I can not attach the file here, but just try adding a new column and make a pivot with that.

[#4]

I uploaded two files and a bit of explanation at the end of my original post, please check for clarification.

[#5 · Accepted solution]

@szilvia_vf 

In on of the tables you have field Year

which most probably appeared when you added Date (jelentkezés dátuma) to the PivotTable. You may add date again (Month and/or Year will appear), ungroup it and after that remove from Rows. Year shall disappear. After that you may add desired calculated item.

[#6]

you're the best! Thank you so much!

[#7]

@szilvia_vf , you are welcome