Calculated item in Pivot table error when field is grouped

Question

Answer 1

Hi @fahim ruwala

According to your image, although you did not select the Dates field for the pivot table, in the fields list, Dates is automatically grouped into the Years field, you need to ungroup this first.

• Please select the Dates and Years fields on Rows area like following image.
  
• Then please select one cell of Dates on pivot table, right-click it, click “Ungroup", the group fields would be removed from fields list, such as the Years field.
  
  
• Now you can remove the Dates field, and insert the Calculated item.
  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

I ungrouped the dates, added the calculated item, but then get an error message when I try to regroup the dates.

It does not make any sense why I get this message after ungrouping and adding the calculated item.

I added Formula1 which is Income-COGS:

Any ideas?

Answer 3

I managed to find the cause of this error, make sure you delete any previous calculated fields/items for that specific Pivot Table. Go to the PivotTable Analyze tab > Calculations group, and delete any field/item there. After this, I was able to group my dates. I hope this helps!

Answer 4

I had a similar problem which brought me here. I solved it by opening a new blank workbook, pasted a new copy of the dataset and then created calculated item in a new pivot table. I think the problem aroused due to working in the same workbook where there were other worksheets and in one of these worksheets, I created groups previously with the same and only dataset in the workbook.

I hope this helps.