Бывают рутинные задачи при работе со сводными таблицами, такие как: создать сводную таблицу, затем прописать разные вычисления, использую формулы в Excel. Это происходит из-за того, что стандартный функционал не позволяет добавить чуть более сложную логику, чем агрегация, поиск макс или мин значений и др. В этой статье я раскрою способы расчета среднего чека, используя надстройку Power Pivot и язык DAX.
В прошлой статье я уже рассказывал, как создать сводную таблицу через Power Pivot, поэтому сейчас будем рассматривать только новые вычисления - расчет среднего чека.
В моем файле таблица, состоящая из 5 столбов:
- ProductKey - id продукта
- OrderQuantity - Количество товаров
- OrderDate - Дата заказа
- SalesOrderNumber - Номер заказа
- SalesAmount - Сумма заказа.
Чтобы посчитать средний чек нам нужно общую сумму заказов разделить на уникальное количество. Для этого создадим новую меру на вкладке Power Pivot, используя язык DAX.
Разберем данное выражение:
- Функция DIVIDE аналог простого знака деления, но с возможностью обработки нулевого значения в знаменателе.
- Первый аргумент - это простая функция суммирования по столбцу
- Второй аргумент - это расчет уникального количества заказа.
- Функция DIVIDE имеет 3ий необязательный аргумент, который выводится, если знаменатель - нуль. По умолчанию - пустое выражение.
В результате, получаем меру, которая считает нам средний чек на каждом уровне иерархии сводной таблицы.
Предположим, у нас появилась задача оценить выполнен ли план по среднему чеку, если план = 24 000. Причем результатом данного меры будет текстовое значение Выполнен/Не выполнен.
Создадим новую меру такого вида:
Меру средний чек мы можем использовать в вычислении, взяз название выражения в квадратные скобки. Функция IF в данном выражении эквивалент функции ЕСЛИ. Получаем такой результат.
На этом все. В одной из следующий статей разберемся как сделать динамический ABC-анализ в сводной таблице. Подписывайтесь, чтобы не пропустить.