Working with Averages in Pivot Table (Calculated Field)

I have the following data :

customer_id customer_name id date contract_value costs profit_extra_work
200027 Company A 00.161.559 08/07/2015 499.5 50.55 0
200027 Company A 00.161.566 08/07/2015 499.5 30 824.9
200027 Company A 00.188.852 04/04/2016 499.5 0 69.39
200027 Company A 00.190.078 30/05/2016 499.5 98.1 158.29
200027 Company A 00.190.291 14/04/2016 499.5 15 346.06
200027 Company A 00.222.221 12/05/2017 499.5 15 5.1
200027 Company A 00.222.229 12/05/2017 499.5 100.35 400.81
200027 Company A 00.161.561 08/07/2015 499.5 45 269.97
200027 Company A 00.185.058 18/02/2016 499.5 15 77.41
200027 Company A 00.190.074 30/05/2016 499.5 37.5 95.92
200027 Company A 00.190.084 30/05/2016 499.5 45 420.89
200027 Company A 00.194.050 01/06/2016 499.5 139.65 0
200027 Company A 00.222.222 12/05/2017 499.5 60 274.88

This is what my Pivot Table looks like:

Row Labels Average of contract_value Sum of costs Sum of profit_extra_work
Company A 499.5 651.15 2943.62 

What I want to do is add a Calculated Field called "Contract profit". This value can be calculated as Average of contract_value - Sum of costs. So I've tried adding a new Calculated Field "Contract profit" with this formula:

= contract_value - costs

But this returns the wrong results (5842.35). The reason is that for contract_value it takes the sum instead of the average. I've tried using AVERAGE(contract_value) - costs but this returns the same incorrect result.

How can I work with the average value of contract_value in Calculated Fields to get the correct value of -151.56?

1 Answer

Microsoft's somewhat opaque help page states that "Custom formulas operate on sum totals, not individual records." ("calculate values in a PivotTable report" This has two implications for your question. (1) references in formula to contract_value will be to sum of contract_value, even though the field is displayed as an average, (2) functions like "AVERAGE" and "COUNT" don't do much of anything because they are operating on a single value -- the sum.

To calculate your own averages, you need to create a new variable in your data table that has a "1" for every entry. Let's call this "helper". "helper" in a custom formula will then be the count of the number of rows. (13 in your example).

So the formula you are looking for is

=(contract_value /helper )-costs

(Note that this will be calculated separately for each row, so if you add a "Company B," for example, you will get the "correct" result for each company.)