Calculated fields in pivot tables - field settings is grayed out.

New Contributor

Hello Experts,

 

I am working on an Excel worksheet of movies revenues, and I am using a pivot table to summarize and perform calculations.

 

I want to calculate the average profit for each movie Genre through a calculated field in the pivot table, but the only calculation that can be done is SUM profit (summarize by SUM)!

 

I cannot change the summary function (summarized by) in the fields settings, it's grayed out and I cannot use the functions such as (Average, Count, Max). I am not able to use the calculated field in any way.

 

How can I solve this problem and get the fields settings activated?! 

 

Note: I am using EXCEL for MAC 2019

 

Screen Shot 2022-04-10 at 8.12.50 AM.png

 

7 Replies

@eyadnt83 Any chance you can share the file? OneDrive, Dropbox or similar. 

Hello Riny, Thanks for the quick response.

please check this link and let me know if you couldn't access it:

https://onedrive.live.com/edit.aspx?resid=B1E92A83BA5B2AB0!116&ithint=file%2cxlsx
Regards,
Soory for that.

I uploaded to wetransfer, I think it will work now:

https://we.tl/t-JLzpxg3K2V

@eyadnt83 Just forget about the calculated field "Profit" as you can indeed not change the "summarise by" setting. In stead, add a Profit column to the source data and drag that one into the value field. Now you can average the profit as desired. I see no other way. If you had been on Excel for Windows, you would have had more options to calculate the average without having to add a column to the source data.

Thanks for the feedback.

Actually i know thaT I can solve it with a helper column added to the source data such as (Profit Column).

But my point, why I cannot change the summarize by settings in my calculated sheets? what is the problem here? Why all the fields are grayed out while they should be activated by default.

@eyadnt83 It just doesn't work like that. Can't really explain why. Perhaps someone else can.