Working with Averages in Pivot Table (Calculated Field)

Question

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?

Solution

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

Comments

[Jonathan]

I hope my answer to your question helped. I'm curious though, about the purpose of your measurement. I can't think of any situation where one would be interested in subtracting total costs from average value.