Мера которая собирает сумму столбцов

С чего начинается мера?

В Power BI есть два отчасти похожих инструмента, но в то же время очень разных: вычисляемые столбцы и меры. Для того чтобы создавать вычисляемые столбцы, нужно хотя бы немного знать DAX и уметь разбивать вычисление на отдельные действия, собирая в некоторый алгоритм. Для мер, которые технически выглядят, на первый взгляд, также, — этих знаний недостаточно. Чтобы использовать меры эффективно, нужно понимать контекст. И вот именно контекст часто вызывает очень много вопросов.

Несправедливо будет говорить, что контекст влияет только на меры. Для вычисляемых столбцов контекст тоже имеет своё значение. В то же время про меры без контекста говорить вообще почти бессмысленно. Давайте разбираться, что такое контекст и как он влияет на вычисления.

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

Контекст фильтра

Суть контекста в том, что он ограничивает набор данных, с которыми работает формула DAX. Например, расчёт выполняется не на основе всей таблицы, а на основе определённого набор строк таблицы, подчиняющегося определённым правилам. Вот некоторые способы ограничения строк, которые влияют на вычисления, все эти способы относятся к контексту фильтра:

1. Фильтры отчёта: фильтры конкретного визуального элемента; этой страницы или всех страниц. На рисунке — фильтр визуального отчёта. Он ограничивает набор строк, на основе которого производится вычисление данных для визуального элемента.

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

2. Срезы. Суть примерно та же, что и у фильтров. Но фильтры главнее. Фильтры фильтруют срезы. Срезы на фильтры не влияют никак.

Можно отключать влияние срезов на определённые визуальные элементы. Можно синхронизировать с соседними страницами отчёта. На рисунке в фильтре — все допустимые варианты значений, на срезе — ограниченные фильтром, на остальных визуальных элементах — ограниченные фильтром и срезом.

3. Кроссфильтрация — фильтрация данных одного элемента при выделении элементов другого элемента. Одни и те же элементы без выделения и с выделением Store 3 — данные на нижней таблице при выделении отфильтровались:
4. Сами элементы визуализаций тоже являются фильтрующими. Они фильтруют набор строк, которые участвуют в вычислении конкретных полей. И тоже являются контекстом фильтра:

5. Функции, используемые при вычислениях:
ALL, RELATED, FILTER, CALCULATE, ALLSELECTED, ALLEXCEPT.

В Power BI помимо контекста фильтра выделяют ещё контекст строки.

Контекст строки

Контекст строки актуален для вычисляемых столбцов. Вычисляемый столбец всегда в результате выдаёт значение для каждой строки таблицы, в которую добавлен. И по умолчанию находится в контексте строки. Мы можем обращаться к столбцу таблицы, в которую добавляем столбец, и если подразумевается значение, то будет взято значение актуальной для вычисления строки:
18 669,00 * 0,05 = 933,45

В следующих статьях мы разберём, как создавать меры, какие они бывают, чем отличаются от столбцов и, самое главное, — зачем им контекст.

Меры в Power Pivot

Меры, также называемые мерами в Power Pivot в Excel 2013, являются вычислениями, используемыми при анализе данных. К примерам, часто встречающимся в бизнес-отчетах, относятся суммы, средние, минимальные и максимальные значения, счетчики или более сложные вычисления, создаваемые с использованием формулы выражений анализа данных (DAX).

В сводной таблице, сводной диаграмме или отчете мера помещается в область «Значения», в которой метки строки и столбца, окружающие ее, определяют контекст значения. Например, при измерении продаж по годам (в столбцах) и регионам (в строках) значение меры вычисляется в зависимости от данного года и региона. Значение меры всегда изменяется в ответ на выбор строк, столбцов и фильтров, обеспечивая нерегламентированный просмотр данных.

Хотя меры и вычисляемые столбцы похожи тем, что основаны на формуле, они отличаются в использовании. Меры чаще всего используются в области Значения сводной таблицы или сводной диаграммы. Вычисляемые столбцы применяются, когда необходимо разместить результаты вычислений в другой области сводной таблицы (например, в строке или столбце сводной таблицы или на оси сводной диаграммы). Очень важно понимать, когда следует использовать меры, а когда — вычисляемые столбцы. Дополнительные сведения см. в статьях Вычисления в Power Pivot и Вычисляются столбцы в Power Pivot.

Общие сведения о мерах

Меры бывают явными и неявными. Это влияет на способы их использования в сводной таблице или сводной диаграмме и в других приложениях, использующих в качестве источника данных модель данных Power Pivot.

Неявная мера создается приложением Excel при перетаскивании такого поля, как Сумма продаж, в область Значения списка полей сводной таблицы. Так как неявные меры создаются Excel, вы можете не знать о том, что создана новая мера. Однако при внимательном изучении списка «Значения» можно заметить, что поле Сумма продаж в действительности является мерой с именем Сумма «Сумма продаж», отображаемой с этим именем в области «Значения» списка полей сводной таблицы, а также в самой сводной таблице.

Неявная мера, созданная в сводной таблице

Неявные меры могут использовать только стандартные агрегатные функции (SUM, COUNT, MIN, MAX, DISTINCTCOUNT или AVG) и формат данных, заданный для этого агрегирования. Кроме того, неявные меры могут использоваться только в сводной таблице или диаграмме, для которой они были созданы.

Неявная мера тесно связана с полем, на котором она основана, что влияет впоследствии на способ удаления или изменения меры.

Явное вычисляемое поле

Явная мера создается пользователем при вводе или выборе формулы в ячейке «Область вычисления» или использовании в окне Power Pivot функции «Автосуммирование». Большинство создаваемых мер будут явными.

Явная мера, созданная в области вычислений Power Pivot

Явные меры можно использовать в любой сводной таблице или сводной диаграмме, в книге и отчетах Power View. Более того, их можно превратить в ключевые показатели эффективности или отформатировать, используя одну из множества строк форматирования, доступных для числовых данных. Команды контекстного меню для функций Создать ключевой показатель эффективности и Формат доступны только при использовании явной меры.

Примечание: После использования меры в качестве KPI ее нельзя использовать для других вычислений. Если вы хотите использовать формулу в вычислениях, необходимо скопировать ее. Дополнительные сведения о ключевых показателях эффективности см. в статье Ключевые показатели эффективности в Power Pivot.

Пример

Менеджер по продажам компании Adventure Works получил задание предоставить прогнозы товарооборота торговых посредников на следующий финансовый год. Он принимает решение базировать свои оценки на объемах продаж предыдущего года, в котором ежегодный прирост достиг 6 % благодаря различным рекламным акциям, проводимым в течение следующих шести месяцев.

Чтобы получить эти данные оценки, он импортирует прошлогодние данные по торговым посредникам и добавляет их в сводную таблицу. Он нашел поле Сумма продаж в таблице «Товарооборот торговых посредников» и перетащил его в область «Значения» списка полей сводной таблицы. Это поле отображается в сводной таблице в виде единичного значения, представляющего суммарный товарооборот торговых посредников начиная с прошлого года. Менеджер обращает внимание, что, даже если он не указал поле, оно было автоматически предоставлено и переименовано в списке полей сводной таблицы в Сумма Сумма продаж. Встроенное агрегатное выражение, добавленное Excel, =SUM(‘FactResellerSales'[SalesAmount]) , выполняет это вычисление. Менеджер переименовал неявную меру Продажи за прошлый год.

Следующий расчет — это прогноз продаж на следующий год, который будет основан на продажах за прошлый год, умноженных на 1,06 с учетом ожидаемого 6-процентного увеличения продаж через торговых посредников. Для этого вычисления необходимо явно создать меру с помощью кнопки Создать вычисляемого поля, чтобы создать вычисление с именем «Прогнозируемые продажи». Она заполнит следующую формулу: =SUM(‘FactResellerSales'[SalesAmount])*1.06 .

Новая мера добавляется в область «Значения» списка полей сводной таблицы. Она также добавляется в таблицу, активную на настоящий момент в списке полей сводной таблицы. В таблице указывается местонахождение меры в книге. Так как менеджер предпочитает, чтобы мера находилась в другой таблице, он отредактировал ее, изменив ее взаимосвязь с таблицей.

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

Именование мер

После создания мер можно изменить их порядок и переименовать их. Тем не менее существуют некоторые ограничения на изменение мер.

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

Имя каждой меры должно быть уникальным в пределах таблицы.

Избегайте использования имен, ранее использовавшихся для вычисляемых столбцов внутри той же книги. Меры и вычисляемые столбцы могут иметь одинаковые имена, но если они не будут уникальными, то возможно появление ошибок вычисления.

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

Так как имя является частью формулы меры, в нем нельзя использовать некоторые символы. Дополнительные сведения см. в подзадаче «Требования к именоимингу» в синтаксис DAX.

Совет: Вы можете объединить меры из нескольких таблиц в одну, создав пустую таблицу, а затем переместив в нее существующие меры или создав новые. Учтите, что при обращении к столбцам в других таблицах может потребоваться включить имена этих таблиц в формулы DAX.

Задачи

В следующей статье содержатся инструкции по созданию явных и неявных вычисляемых полей.

Глава 26. Продвинутые вычисляемые столбцы в Power Pivot

Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Вычисляемые столбцы не являются сильной стороной DAX. Меры – это да! (поэтому почти вся книга посвящена им). А вот столбцы есть и в обычном Excel, поэтому, по большому счету, здесь мало что добавишь. На самом деле, вычисляемые столбцы в Power Pivot даже немного сложнее, чем обычные столбцы Excel, потому что Power Pivot не хватает ссылки в стиле » A1 » . А начнем мы с относительно простых трюков.

Рис. 26.1. Продажи в зависимости от температурного диапазона

Скачать заметку в формате Word или pdf, примеры в формате Excel

Группировка по значениям

Наш любимый пример группировки – объем продаж в зависимости от температуры. Мы импортируем таблицу данных о температуре (по дням), связываем ее с таблицей продаж, а затем выводим в сводную таблицу меру [Sales per Day], в зависимости от температурного диапазона (рис. 26.1). Вот формула, которую мы используем в вычисляемом столбце TempRange таблицы Temperature:

Рис. 26.2. Столбец группировки температуры по диапазонам (значения градусов в столбце AvgTemp указано по шкале Фаренгейта; t_°С = (t_F – 32)/1,8; таким образом, холодно – ниже 4°С, прохладно = 4–10°С, тепло = 10–21°С, жарко – более 21°С)

Что не так с нашей сводной таблицей на рис. 26.1? Диапазоны отсортированы по алфавиту, а хотелось бы по возрастанию температур. Попробуем отсортировать столбец TempRange по значениям температуры в столбце AvgTemp. Для этого в Power Pivot перейдите в таблицу Temperature, выделите столбец TempRange, и кликните кнопку Сортировка по столбцам. В открывшемся окне выберите Сортировать по: AvgTemp.

Рис. 26.3. Сортировка диапазонов столбца TempRange по значениям температуры в столбце AvgTemp

Это приводит к ошибке:

Рис. 26.4. Для сортировки каждому значению в столбце TempRange должно соответствовать только одно значение в столбце AvgTemp; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Ну что ж, добавим еще один столбце в таблицу Temperature с уникальным кодом диапазона:

Рис. 26.5. Кандидат на роль сортировочного столбца – TempRangeSeguence

Повторите сортировку (см. рис. 26.3). Но теперь установите Сортировать по: TempRangeSeguence. Получим желаемый результат:

Рис. 26.6. Теперь диапазоны выстроились в правильном порядке

Сортировка по вычисляемому ключу

Рассмотрим столбец QtrYearLabel в таблице Periods:

Рис. 26.7. Обратите внимание, как каждому значению в столбце QtrYearLabel соответствует несколько значений в столбце PeriodID

Если мы хотим «правильно» сортировать по столбцу QtrYearLabel, столбец PeriodID не подходит, и нам нужен новый вычисляемый столбец. Но на этот раз функция SWITCH() не спасет нас (поняли, почему?). Нам нужен какой-то шаблон для расчета уникальной последовательности кварталов. Например, такой: [Year] * 4 + [Qtr]. А в общем виде: <Year column> * <number of periods per year> + <period column>. Здесь периодом может быть квартал (4 за год), месяц (12), неделя (52), семестр (2) и т.д.

Рис. 26.8. QtrSequence – уникальный столбец-идентификатор для сортировки столбца QtrYearLabel

Суммирование в таблице поиска

Как правило, мы используем меры, чтобы суммировать продажи по продуктам. Но бывают ситуации, когда полезно создать вычисляемый столбец в таблице Products, отражающий продажи по каждому продукту. Вы уже видели такой столбец в главе о контексте строк:

Рис. 26.9. Функция CALCULATE превращает контекст фильтра в контекст строки, и возвращает общий объем продаж для каждого продукта

Усложним задачу: добавим в таблицу Products столбец с объемом продаж, соответствующим общим продажам для категории, к которой принадлежит продукт.

Использование функции EARLIER()

Это сложная для понимания функция, но, чтобы начать ее использовать, достаточно познакомиться с шаблоном:

Благодаря этой формуле в столбце TotalCaregorySales в каждой строке выводится общий объем продаж всех продуктов соответствующей категории (рис. 26.10). Для аксессуаров он один, для велосипедов – другой, но для одной и той же категории – одинаков.

Рис. 26.10. Каждая строка в соответствующей категории возвращает сумму продаж всей категории

Обратите внимание, что в формуле в строке 4 нам не нужно использовать снятие фильтра с помощью ALL(Products), как мы делали ранее. Дело в том, что ранее у нас была формула меры, которая наследует фильтры из сводной таблицы. А сейчас у нас формула вычисляемого столбца, для которой не существует исходного контекста фильтра (столбцы в Power Pivot не имеют контекста фильтра). Таким образом, нет необходимости очищать фильтр с помощью ALL().

Прежде чем разобраться в том, какое условие проверяется в строке 5, еще раз проговорим, как действует описанные выше формула. Функция CALCULATE суммирует продажи по всем строкам, отобранным функцией FILTER. Мы могли бы просто суммировать =SUM(Sales[SalesAmt]), но тогда в каждой строке таблицы Products было бы одно и то же число. Функция SUM() не знает контекста строки. А вот CALCULATE использует контекст строки, и возвращает сумму только по тому ProductKey, который представлен в строке.

Функция FILTER не использует контекст строки, т.е. любое условие внутри нее будет работать на всей таблице Products. При этом функция FILTER – итератор, т.е. она будет проходить таблицу Products строка за строкой. Функция EARLIER как бы выходит наружу этой вложенности (FILTER внутри CALCULATE), и использует контекст строки функции CALCULATE. Таким образом, внутри функции FILTER берется вся таблица Products, а затем строка за строкой отбираются те, для которых категория совпадает с той, что в строке, для которой рассчитывается вся сумма CALCULATE(SUM(…);FILTER(…)).

Проиллюстрируем картинками. CALCULATE, скажем, для строки с ProductKey=597, задает контекст строки Category = » Bikes » :

Рис. 26.11. Исходный контекст строки

Вложенная внутрь функции CALCULATE функция FILTER (рис. 26.12) по мере прохождения по таблице Products оценивает условие для каждой строки и, если сравнение дает ИСТИНА, то включает эту строку в возвращаемую таблицу.

Рис. 26.12. Итеративная функция FILTER, проходя по таблице Products, проверяет условие

Например, когда FILTER находится в строке ProductKey=234 (рис. 26.12), условие в формуле в строке 5 сравнивает Products[Category] = » Clothing » (текущая строка функции-итератора FILTER), с контекстом строки для CALCULATE. Именно в этом назначение функции EARLIER() – выйти из контекста строки вложенной функции, и обратиться к контексту строки внешней функции.

Для строки ProductKey=234 контекст строки внутри и вне FILTER не совпадает, и строка не будет возвращена, чтобы принять участие в подсчете продаж велосипедов:

Рис. 26.13. Функция EARLIER помогает нам перейти к «внешнему» контексту строки, или, как говорят, к контексту строки, существующему на один уровень выше текущей вложенности

Если все это слишком сложно, не переживайте. Просто применяйте шаблон. Понимание придет с практикой.

Power Pivot Базовый №2. Простые меры

В этом уроке мы научимся создавать простые меры в Power Pivot. Помимо этого мы будем активно пользоваться сводными таблицами, поэтому если вы в них не разбираетесь, то начнете их понимать намного лучше.

В этом уроке мы изучим/повторим:

• Как создавать и редактировать меры в Power Pivot
• Как создать сводную таблицу
• Как в сводной отобразить только Топ лучших/худших
• Как создать сводную диаграмму
• Функция SUM
• Функция DISTINCTCOUNT
• Функция COUNTROWS

Динамика суммы продаж по годам

Сначала создадим меру Sum of Sales:

После этого создадим сводную таблицу и линейную сводную диаграмму к ней. В строках будет номер месяца, в столбцах год, в значениях — наша созданная мера:

Сумма продаж по подкатегориям

Используя ту же самую меру создадим другую таблицу. На этот раз в строках будет находиться подкатегория, а в значениях созданная мера:

Выручка и прибыль по регионам и категориям

Создадим меру, которая вычисляет прибыль:

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

Динамика прибыли по подкатегориям

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

Прибыльность каждого товара

Создадим меру Profitability, в которой вычислим прибыльность каждого товара:

Создадим сводную, в которой в строки добавим категорию и название товара, а в значения прибыль, выручку, прибыльность:

Количество проданных уникальных товаров из каждой подкатегории за каждый год

Мера для вычисления количества уникальных проданных товаров:

Создадим сводную таблицу, в которой в строки добавим подкатегорию, в столбцы год, а в значения новую созданную меру: