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?
Best 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
(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.)