6

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
  • 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.
    – Jonathan
    Jul 25, 2017 at 18:10

1 Answer 1

5

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .