Apr 09 2022 10:17 PM
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
Apr 09 2022 10:34 PM
@eyadnt83 Any chance you can share the file? OneDrive, Dropbox or similar.
Apr 09 2022 10:44 PM
Apr 09 2022 10:54 PM
@eyadnt83 Doesn't work. Sorry.
Apr 09 2022 11:28 PM
Apr 09 2022 11:56 PM - edited Apr 09 2022 11:57 PM
@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.
Apr 10 2022 12:05 AM - edited Apr 10 2022 12:06 AM
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.
Apr 10 2022 12:31 AM
@eyadnt83 It just doesn't work like that. Can't really explain why. Perhaps someone else can.