Работа со средними в сводной таблице (вычисляемое поле)

Question

У меня есть следующие данные:

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

Вот как выглядит моя сводная таблица:

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

То, что я хочу сделать, это добавить Расчетное поле под названием "Контракт прибыль". Это значение может быть рассчитано как Average of contract_value - Sum of costs . Итак, я попытался добавить новое Расчетное поле "Прибыль по контракту" с помощью этой формулы:

= contract_value - costs

Но это возвращает неправильные результаты (5842,35). Причина в том, что для contract_value он принимает сумму вместо среднего. Я пытался использовать AVERAGE(contract_value) - costs но это возвращает тот же неверный результат.

Как я могу работать со средним значением contract_value в вычисляемых полях, чтобы получить правильное значение -151,56?

Answer 1

На несколько непрозрачной справочной странице Microsoft говорится, что «пользовательские формулы работают с итоговыми суммами, а не с отдельными записями». ("Рассчитать значения в отчете сводной таблицы" Это имеет два значения для вашего вопроса. (1) ссылки в формуле на contract_value будут представлять собой сумму contract_value, даже если поле отображается как среднее значение, (2) функции, такие как "AVERAGE" и "COUNT" ничего не делает, потому что они работают с одним значением - суммой.

Чтобы рассчитать свои собственные средние значения, вам нужно создать новую переменную в вашей таблице данных, которая имеет "1" для каждой записи. Давайте назовем это "помощником". "Помощник" в пользовательской формуле будет подсчет количества строк. (13 в вашем примере).

Итак, формула, которую вы ищете

=(contract_value /helper )-costs

(Обратите внимание, что это будет рассчитываться отдельно для каждой строки, поэтому, если вы добавите, например, "Компанию Б", вы получите "правильный" результат для каждой компании.)