Field Wrong Calculations

Question

When using an Excel pivot table to examine a dataset, I wanted to add a calculated field that would allow me to determine the profit by dividing the revenue by the budget. I have seen that the field is not operating in accordance with its purported formula; instead, it is adding the data from the raw data while completely ignoring the formula. Please help, I attempted to figure it out but couldn't come up with a solution.

It subducts the cells from the raw data but I want the formula to subduct them using the Averages that I generated..

enter image description here

enter image description here

I attempted to adjust the formula and add "Average" before each column, but it was unsuccessful. There is a Google Sheets option named Custom. which omitted the aggregating processes in favour of the formula. My issue is that the Averages that I am aiming for are not being picked up since, although the formula appears to be functioning, it is utilising cells from the raw data rather than the cells I want to utilise.

Answer 1

You can accomplish your goals in one of two ways:

You should first add a new column to your table called "Dummy," say, with a value of 1 in each row. Next you may use the Calculated Field that is listed below:

='Box Office Revenue ($)' / Dummy - 'Budget ($)' / Dummy

Add your table to the Data Model and use the following measure within Power Pivot:

=AVERAGE ( Table1[Box Office Revenue ($)] ) - AVERAGE ( Table1[Budget ($)] )