Forum Discussion

Copper Contributor

Apr 10, 2022

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

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

Copper Contributor

Apr 10, 2022

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,

Riny_van_Eekelen

Platinum Contributor

Apr 10, 2022

eyadnt83 Doesn't work. Sorry.

eyadnt83
Copper Contributor
Apr 10, 2022
Soory for that.

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

https://we.tl/t-JLzpxg3K2V
- Riny_van_Eekelen
  Platinum Contributor
  Apr 10, 2022
  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.
  - eyadnt83
    Copper Contributor
    Apr 10, 2022
    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.