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

[eyadnt83]

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

[#2]

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

[#3]

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,

[#4]

@eyadnt83 Doesn't work. Sorry.

[#5]

Soory for that.

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

https://we.tl/t-JLzpxg3K2V

[#6]

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

[#7]

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.

[#8]

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