SOLVED

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

Copper Contributor

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.

szilvia_vf_0-1636152800205.png

 

Here is my pivot:

- columns D and G as rows

- H as values

2021-11-05_23h54_35.png

 

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.

 

2021-11-05_23h59_41.png

 

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...

6 Replies

@szilvia_vf 

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

image.png

Perhaps you may share sample file?

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.
I uploaded two files and a bit of explanation at the end of my original post, please check for clarification.
best response confirmed by szilvia_vf (Copper Contributor)
Solution

@szilvia_vf 

In on of the tables you have field Year

image.png

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.

you're the best! Thank you so much!
1 best response

Accepted Solutions
best response confirmed by szilvia_vf (Copper Contributor)
Solution

@szilvia_vf 

In on of the tables you have field Year

image.png

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.

View solution in original post