И вообще DAX.
Или почему меры DAX лучше вычисляемых полей.
Привет, коллега, на связи Ленивый аналитик. Ранее мы начали знакомиться с возможностями моделей данных, надстройки Power Pivot в Excel и языка DAX.
DAX в Excel, без которого можно, но плохо...
Делаем CALCULATE, или что там по СУММЕСЛИ в DAX
И возможно ты задаешься вопросом - зачем это всё? Ведь на выходе мы имеем привычную нам сводную табличку. А меры DAX - это по сути вычисляемые поля в этой сводной табличке. Для чего столько мороки с изучением нового языка?
Сейчас я приведу тебе пример кейса, который не решается при помощи вычисляемых полей, но при этом вполне компактно можно "победить" с помощью DAX.
Поупражняемся на таблице со списком сотрудников и их зарплатами из предыдущей статьи.
Задача: посчитать отклонение заработной платы каждого сотрудника от средней зарплаты по компании.
Считать будем по формуле:
Зарплата сотрудников у нас есть. Необходимо получить среднюю зарплату по компании.
Вариант 1 - решаем задачу с помощью вычисляемых полей
Создадим сводную таблицу из нашей плоской таблицы.
В Строки добавим имя сотрудника, в Значения - зарплату каждого сотрудника из поля Заработная плата.
Теперь попробуем получить среднюю зарплату по всем сотрудникам. Для этого создадим вычисляемое поле при помощи стандартной функции СРЗНАЧ.
Добавим новое поле Средняя зарплата в значения сводной таблицы.
И... опытные эксельщики уже догадались, что будет)))
Для каждого сотрудника значение поля Средняя зарплата равно его собственной зарплате.
Это произошло потому, что в сводной таблице вычисления происходят в пределах каждой строки. Если бы мы в строки добавили отдел, а не сотрудника - была бы средняя зарплата по отделу. Но - нам нужно рассчитать для каждого сотрудника.
(если кого-то смутит, что в названии поля указано "Сумма по полю Средняя зарплата" - то изменение на среднее значение меняет только итог, для каждой строки значение будет то же. Потому что вычисляется для строки)
Поэтому решить эту задачу при помощи стандартной сводной таблицы лично я не вижу возможности. Имею ввиду - именно внутри сводной таблицы.
Можно лишь рассчитать отдельно среднее значение зарплаты и рассчитать отклонение от среднего в столбце рядом со сводной таблицей. Как-то так, например:
Однако, этот вариант имеет ряд ограничений, связанных с изменением набора столбцов и строк. И просто неудобен. И непрофессионален.
Вариант 2 - решаем задачу с помощью мер DAX
Загрузим таблицу в модель данных (если не умеете, можно подсмотреть в этой статье в разделе "Добавим таблицу в модель данных в Excel").
Из модели данных создадим сводную таблицу, в строки которой добавим поле Сотрудник, а в значения - поле Заработная плата.
Теперь создадим меру Средняя зарплата (как создать меру, описано этой статье)
В поле Формула запишем следующую конструкцию:
=CALCULATE(AVERAGE([Заработная плата]);ALL('Таблица1'))
Первый аргумент функции CALCULATE - это всегда агрегирующая функция, которая применяется к определенному столбцу. Здесь мы считаем среднее значение (AVERAGE) по столбцу Заработная плата.
Второй аргумент - это фильтр. Но нам наоборот - нужно убрать все фильтры. Чтобы среднее значение считалось без учета структуры таблицы.
И здесь на помощь приходит функция ALL, которая снимает фильтры. В качестве аргумента укажем название таблицы.
Добавим созданную меру в значения сводной таблицы.
Средняя зарплата по компании теперь выводится для каждого сотрудника.
Осталось немного - создать меру для расчета отклонения зарплаты сотрудника от средней зарплаты по компании.
Обратите внимание, что к столбцу "Заработная плата" применена агрегирующая функция SUM, а мера "Средняя Зарплата" указана без агрегирующей функции. Это связано с тем, что столбцы без агрегации не могут участвовать в расчетах мер, а меры уже имеют агрегацию и указываются без нее.
Готово. Мы получили отклонение зарплаты сотрудника от средней в сводной таблице.
Надеюсь, этим небольшим, но довольно жизненным примером я доказала вам, что изучать DAX в Excel - нужно.
А чтобы не пропускать новые статьи, подписывайтесь здесь и в моем ТГ Ленивый аналитик.