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

Question

Hello Experts,&nbsp;I am working on an Excel worksheet of movies revenues, and I am using a pivot table to summarize and perform calculations.&nbsp;I want to calculate the average profit for each movie Genre&nbsp;through a calculated field in the pivot table, but the only calculation that can be done is SUM profit (summarize by SUM)!&nbsp;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.&nbsp;How can I solve this problem and get the fields settings activated?!&nbsp;&nbsp;Note: I am using EXCEL for MAC 2019&nbsp;&nbsp;

riny_van_eekelen · Answer

eyadnt83&nbsp;Any chance you can share the file? OneDrive, Dropbox or similar.&nbsp;

riny_van_eekelen · Answer

eyadnt83&nbsp;Doesn't work. Sorry.

eyadnt83 · Answer

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&amp;ithint=file%2cxlsxRegards,

eyadnt83 · Answer

Soory for that.I uploaded to wetransfer, I think it will work now:https://we.tl/t-JLzpxg3K2V

riny_van_eekelen · Answer

eyadnt83&nbsp;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.

Forum Discussion

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

7 Replies

Resources