power pivot что такое мера

Создание меры в Power Pivot

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

Некоторые меры можно легко создать, например меры, использующие стандартную агрегатную функцию, например СУММ или СПБ, и созданные с помощью функции авто суммирования в окне Power Pivot, или неявные меры, созданные в Excel путем простого перетаскиванием поля в область значений. Другие могут быть более сложными, например для следующих связей или фильтрации результатов, требующие формулы, создаваемой с помощью DAX.

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

Создание неявной меры в Excel

Щелкните сводную таблицу.

В списке Поля сводной таблицы перетащите поле в область Значения.

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

Создание меры в окне Power Pivot с использованием автосуммирования

Щелкните Вычисления > Автосумма и выберите агрегат.

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

Создание меры в окне Power Pivot с помощью области вычислений

Щелкните В начало > Просмотр > Область вычисления.

Щелкните пустую ячейку в области вычислений.

В строке формул в верхней части таблицы введите формулу в формате имя меры>: формула>

Нажмите клавишу ВВОД, чтобы принять формулу.

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

Создание меры с помощью диалогового окна «Мера» в Excel

В окне Excel выберите Power Pivot > Вычисления > Меры > Создать меру.

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

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

В поле Имя меры введите имя.

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

В текстовом поле Формула поместите курсор за знаком равенства (=) и введите формулу.

Нажмите кнопку Проверить формулу для проверки.

В списке Категория выберите тип меры.

Тип меры не влияет на работу формулы. Он нужен только для информации.

Если формула выдержала проверку, нажмите кнопку ОК.

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

Источник

Меры в 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 % благодаря различным рекламным акциям, проводимым в течение следующих шести месяцев.

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

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

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

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

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

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

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

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

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

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

Задачи

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

Источник

Введение в DAX-формулы

DAX-формулы или Data Analysis Expressions — выражения для анализа данных в Microsoft Power BI, в Analysis Services и Power Pivot в Excel. DAX-формулы позволяют, по аналогии с формулами Excel, выполнять вычисления и настраивать произвольную фильтрацию и представление данных в таблицах.

Язык DAX есть в следующих приложениях:

Power Pivot в Excel

SQL Server Management Studio

В этой статье описание DAX приводится для Power Pivot в Excel и Power BI.

Строение DAX-формул

В DAX-формулах почти также: названия таблиц обычно пишут в одинарных кавычках (или без кавычек, если имя таблицы написано латинскими буквами без пробелов и цифр в начале). Названия столбцов пишут в квадратных скобках:

Математические операторы:
& + — / * = > ‘ Таблица ‘ [Столбец]

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

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

Чтобы использовать меру в других вычислениях, ее название пишут в квадратных скобках.
Пример: МераВ = [МераА] + 100

Примечание о записи формул и разделителей:

Базовые DAX-формулы

В языке DAX существует множество формул или функций, позволяющих выполнять продвинутые аналитические вычисления. Эти функции относятся к разным группам — агрегирующие, логические, математические, для работы с текстом, со временем и др. Полный список функций можно посмотреть на сайте Microsoft. Для начала разберем наиболее часто встречающиеся (на наш взгляд) формулы.

1. SUM

2. BLANK

Формула BLANK возвращает пустое значение. Пустое значение в DAX – это отсутствие значения, а не привычный нам в Excel 0 (ноль) или пустая строка («»).

Записывается формула очень просто:

Никаких аргументов у нее нет.

3. IF

Формула IF – это логическая формула, аналог ЕСЛИ в Excel. Она проверяет условие и, если условие выполнено, возвращает одно значение, иначе – другое значение.

4. DIVIDE

— это значение, которое будет выводиться, когда деление на ноль приводит к ошибке. Его указывать необязательно, по умолчанию формула возвращает пустое значение.

5. MIN и MAX

Формулы MIN и MAX – это агрегирующие формулы. Они находят минимальное и, соответственно, максимальное значение из столбца или из двух выражений (выражение должно вычислять единичное значение).

Если вы думаете, что эти формулы нужны для поиска наименьшего или наибольшего значения показателя, то вы правы. А еще MIN и MAX часто применяются в вычислениях, связанных с датами. То есть они вам точно пригодятся – выписываем и берем на вооружение!

6. DISTINCTCOUNT

DISTINCTCOUNT – полезная формула. Она подсчитывает количество уникальных значений в столбце таблицы.

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

7. COUNTROWS

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

Давайте разберем, какие еще вычисления можно делать с помощью DAX.

Функции агрегирования

Кроме таких формул существуют еще другие, похожие на них с окончаниями «А» и «Х». Функции с «A» на конце обрабатывают непустые ячейки. Формулы с «X» позволяют выполнять вычисления по строкам.

Для чего нужны построчные вычисления в формулах с «X»? Если создать меру так:
Мера = SUM ( ‘ Данные ‘ [Цена]) * SUM ( ‘ Данные ‘ [Количество]), вычисления будут некорректные.

Необходимы вычисления по строкам:
Мера = SUMX ( ‘ Данные ‘ ; [Цена] * [Количество])

Логические функции

Логические функции в DAX довольно просты для понимания. Они выполняют то же, что в «обычном» Excel. Чтобы вам было проще разобраться, собрали в таблице часто используемые логические функции.

Формула SWITCH может выбрать 255 вариантов значений в зависимости от того, чему равна влияющая ячейка.
Например, мы можем записать формулу для времени года так:

Со SWITCH все проще:

и так далее – уже проще и понятнее.

Математические функции

Чтобы хорошо разобраться в математических формулах, вспомните, какие именно из них вы чаще всего применяете в вычислениях и найдите аналогичные в DAX. Про формулы SUM и DIVIDE мы уже писали, а далее в таблице собраны другие популярные формулы.

Текстовые функции

Текстовые функции в DAX основаны на аналогичным списке функций в Excel. Наиболее часто используемые функции собраны в таблице.

Функции для работы с датами

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

Функции фильтрации

В определениях DAX функция CALCULATE относится к функциям фильтрации. CALCULATE работает по аналогии с формулой СУММЕСЛИМН при указании в этой формуле суммы и условия отбора – фильтра:

Самыми яркими представителями функций фильтрации являются FILTER и ALL :

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

Источник

Что такое Power Query / Pivot / Map / View / BI и зачем они пользователю Excel

Термины «Power Query», «Power Pivot», «Power BI» и прочие «пауэры» все чаще всплывают в статьях и материалах о Microsoft Excel. По моему опыту, далеко не все ясно представляют себе что скрывается за этими понятиями, как они между собой взаимосвязаны и как могут помочь простому пользователю Excel.

Давайте проясним ситуацию.

Power Query

Power Query встречается в двух вариантах: как отдельная надстройка для Excel 2010-2013, которую можно скачать с официального сайта Microsoft и как часть Excel 2016. В первом случае после установки в Excel появляется отдельная вкладка:

В Excel 2016 весь функционал Power Query уже встроен по умолчанию и находится на вкладке Данные (Data) в виде группы Получить и преобразовать (Get & Transform) :

Возможности этих вариантов совершенно идентичны.

Основное окно Power Query обычно выглядит примерно так:

Power Pivot

Общие принципы работы в Power Pivot следующие:

Главное окно Power Pivot выглядит примерно так:

А так выглядит Модель Данных, т.е. все загруженные таблицы с созданными связями:

У Power Pivot есть ряд особенностей, делающих её уникальным инструментом для некоторых задач:

К сожалению, пока что Power Pivot входит не во все версии Excel. Если у вас Excel 2010, то скачать её можно бесплатно с сайта Microsoft. А вот если у вас Excel 2013-2016, то всё зависит от вашей лицензии, т.к. в некоторых вариантах она включена (Office Pro Plus, например), а в некоторых нет (Office 365 Home, Office 365 Personal и т.д.) Подробнее об этом можно почитать тут.

Power Maps

Эта надстройка впервые появилась в 2013 году и первоначально называлась GeoFlow. Она предназначена для визуализации гео-данных, т.е. числовой информации на географических картах. Исходные данные для отображения берутся все из той же Модели Данных Power Pivot (см. предыдущий пункт).

Ключевые особенности Power Map:

Power View

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

На сайте Microsoft, кстати, есть весьма приличный обучающий курс по Power View на русском языке.

Power BI

В Power BI Desktop можно:

Выглядит это примерно так:

На iPhone, например, созданный выше отчет выглядит так:

Источник

Power Pivot. Основы. Простые меры

Описание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник