Добавление вычисляемых столбцов в Excel Pivot без подробностей

Question

Есть ли способ добавить вычисляемое поле, которое будет применяться только к итоговому столбцу, без добавления ненужных подробностей для каждого элемента столбца?

Рассмотрим следующий пример.

Данные следующие:

Year    Product     Units   Price   Revenue
2013    Porduct A   100     1.5     150
2013    Porduct B   150     1.6     225
2013    Porduct C   200     1.7     300
2013    Porduct D   250     1.8     375
2013    Porduct E   300     1.9     450
2014    Porduct A   150     1.5     225
2014    Porduct B   200     1.6     300
2014    Porduct C   250     1.7     375
2014    Porduct D   300     1.8     450
2014    Porduct E   350     1.9     525

Результирующий разворот выглядит следующим образом:

Теперь, скажем, я хочу добавить скидку 10% для каждого продукта в целом, однако, когда я добавляю вычисляемое поле, я получаю ненужные детали скидки для каждого года отдельно:

Что я действительно хочу, так это избавиться от годовой детализации и оставить расчет только для общего количества:

Но я хотел бы добиться этого, не скрывая столбцы, а просто добавляя поле, как я обычно делал бы без точки поворота. Причина в том, что может быть много последовательных вычислений, которые я хотел бы выполнить без горизонтального роста сводки (особенно, когда я делаю ежемесячный анализ, он расширяет результирующую таблицу на 12 столбцов каждый раз, когда вычисляемое поле добавлено.

Answer 1

Стандартные сводные таблицы из коробки не позволяют этого.

Сначала я опишу, как вы могли бы обойти это, используя обычные сводные таблицы, во-вторых, я покажу вам, как вы можете добиться этого с помощью надстройки PowerPivot.

---

Вариант 1: две стандартные сводные таблицы

Я бы занялся этим, просто расположив две сводные таблицы рядом друг с другом, одну с годами в качестве метки столбца, а другую без (эта вторая таблица расположена на один ряд ниже, чтобы они выстроились в линию).

Вы должны убедиться, что столбцы меток строк отсортированы и отфильтрованы одинаково. Тогда вы можете просто скрыть столбец меток на второй таблице.

(На этом скриншоте я вручную добавил метки "Всего" в строке 3)

---

Вариант 2. Использование PowerPivot

Используя PowerPivot и DAX, вы можете создать все эти столбцы как отдельные меры, чтобы вы могли довольно точно контролировать применяемые фильтры.

Сначала убедитесь, что у вас установлен PowerPivot - Excel не поставляется с ним отдельно от некоторых выпусков Excel 2013. Я не буду проходить через процесс установки, но быстрый поиск должен помочь, если вы застряли.

Выберите диапазон данных и нажмите « Create Linked Table на панели инструментов PowerPivot.

Должно появиться зеленое окно PowerPivot. По умолчанию ваша таблица получит просто имя Table1 - давайте переименуем ее в ProductSales, чтобы сделать ее более значимой.

Нажмите кнопку « PivotTable в центре зеленой ленты и разместите ее где угодно. Теперь нам нужно создать некоторые меры. PowerPivot использует тип кода под названием DAX (Data Analysis eXpressions), который очень похож на обычные формулы Excel.

Вернувшись в Excel, начните с добавления продукта в Row Labels в сводной таблице. Теперь нажмите кнопку « New Measure на вкладке ленты PowerPivot.

В появившемся окне установите для параметра Measure name (all PivotTables) значение Всего единиц и в текстовом поле Description введите:

=sum(ProductSales[Units])

Создайте другую меру, которая называется Единицы 2013. Введите этот код:

=CALCULATE([Units Total],ProductSales[Year]=2013)

Функция CALCULATE() в DAX позволяет вам применять (несколько) фильтров к агрегированному выражению - поэтому здесь мы фильтруем только что созданную меру Units Total, к соответствующему году. Повторите эти действия для Единиц 2014, а затем повторите то же самое для Выручки, Дохода 2013 и Дохода 2014.

Теперь вы можете упорядочивать и форматировать столбцы по своему усмотрению - чтобы имитировать снимки экрана выше, я вручную установил столбцы 2013 и 2014 в серый цвет.