Excel DAX — вычисляемые поля / показатели

Вычисляемое поле в таблице в модели данных — это поле, полученное по формуле DAX. В более ранних версиях Power Pivot вычисленное поле называлось мерой. В Excel 2013 оно было переименовано в вычисляемое поле. Тем не менее, он переименован обратно в измерение в Excel 2016. Если вы обращаетесь к какой-либо документации, вы можете наблюдать смешивание этих двух терминов. Обратите внимание, что термины вычисляемое поле и мера являются синонимами. В этом уроке мы используем термин вычисляемое поле.

Понимание вычисляемых полей

Вычисляемое поле — это формула, созданная специально для использования в сводной таблице (или сводной диаграмме).

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

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

• Вычисляемое поле может использоваться только в области VALUES сводной таблицы.

• Вычисляемый столбец с рассчитанными результатами можно использовать также в областях ROWS, COLUMNS и FILTERS.

Вычисляемое поле может использоваться только в области VALUES сводной таблицы.

Вычисляемый столбец с рассчитанными результатами можно использовать также в областях ROWS, COLUMNS и FILTERS.

Сохранение рассчитанного поля

Рассчитанное поле будет сохранено вместе с исходной таблицей в модели данных. Он отображается в списке полей Power PivotTable или Power PivotChart как поле в таблице.

Использование вычисляемого поля

Чтобы использовать вычисляемое поле, вы должны выбрать его из списка Поля сводной таблицы. Вычисленное поле будет добавлено в область ЗНАЧЕНИЯ, и будет вычислена формула, используемая для вычисляемого поля. Результат создается для каждой комбинации полей строки и столбца.

Расчетное поле — пример

Рассмотрим следующую модель данных для данных Олимпийских игр —

Как видно на снимке экрана выше, в таблице «Результаты» есть поле «Медаль», в котором содержатся значения — золото, серебро или бронза для каждой строки, содержащей комбинацию «Спорт — Событие — Страна — Дата». Предположим, вы хотите подсчет медалей для каждой страны, затем вы можете создать вычисляемое поле Medal Count по следующей формуле DAX —

Medal Count := COUNTA([Medal])

Создание вычисляемого поля в таблице

Чтобы создать вычисляемое поле Medal Count в таблице Results, сделайте следующее —

• Щелкните по ячейке в области расчета под столбцом «Медаль» в таблице «Результаты». Ячейка будет выделена.

• Введите Medal Count: = COUNTA ([Medal]) в строке формул.

Щелкните по ячейке в области расчета под столбцом «Медаль» в таблице «Результаты». Ячейка будет выделена.

Введите Medal Count: = COUNTA ([Medal]) в строке формул.

Нажмите Ввод.

Как видно на скриншоте выше, вычисленное поле появляется в выбранной ячейке, показывая значение 34,094. Это число — общее количество строк в таблице результатов. Следовательно, это не имеет большого смысла на первый взгляд. Как обсуждалось ранее, реальное использование вычисляемого поля можно увидеть, только добавив его в Power PivotTable или Power PivotChart.

Использование вычисленного поля в сводной таблице Power

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

• Нажмите на сводную таблицу на ленте в окне Power Pivot.
• Нажмите на сводную таблицу в раскрывающемся списке.

Откроется диалоговое окно «Создать сводную таблицу».

• Нажмите на существующую таблицу.
• Выберите место, где вы хотите разместить сводную таблицу.

Будет создана пустая сводная таблица.

• Щелкните таблицу «Результаты» в списке «Поля сводной таблицы».
• Нажмите на поля — Страна и Количество медалей.

Как вы можете заметить, счетчик медалей добавляется в область ЗНАЧЕНИЙ, а страна — в область строк. Сводная таблица создается со значениями поля Страна, появляющимися в строках. И для каждой строки рассчитывается и отображается значение количества медалей. Таким образом, вычисляемое поле оценивает используемую формулу DAX и отображает значения.

• Добавьте поле Спорт из таблицы результатов в область ROWS.

Как вы можете видеть на скриншоте выше, количество медалей рассчитывается для каждой страны — в зависимости от вида спорта и промежуточного итога для самой страны.

Вот как DAX дополняет функции Power.

Типы вычисляемых полей

Существует два типа вычисляемых полей — неявные и явные.

• Неявное вычисляемое поле создается в области списка Power PivotTable Fields.

• Явное вычисляемое поле создается либо в таблице в окне Power Pivot, либо из ленты PowerPivot в окне Excel.

Неявное вычисляемое поле создается в области списка Power PivotTable Fields.

Явное вычисляемое поле создается либо в таблице в окне Power Pivot, либо из ленты PowerPivot в окне Excel.

Создание неявного вычисляемого поля

Неявное вычисляемое поле может быть создано двумя способами, оба на панели Power PivotTable Fields.

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

Вы можете создать поле Count of Medal из поля Medal в списке полей сводной таблицы следующим образом:

• Отмените выбор поля «Количество медалей».
• Щелкните правой кнопкой мыши на поле Медаль.
• Нажмите Add to Values ​​в раскрывающемся списке.

Количество медалей появляется в области значений. Столбец Количество медалей будет добавлен в сводную таблицу.

Создание неявного вычисляемого поля в области VALUES

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

• Нажмите стрелку вниз в поле Count of Medal в области VALUES.
• Нажмите Настройки поля значения в раскрывающемся списке.

Откроется диалоговое окно «Настройки поля значений».

• Введите% Медали в поле Пользовательское имя.
• Перейдите на вкладку «Показать значения как».
• Щелкните поле под Показать значения как.
• Нажмите% от общего количества родительских строк.

• Нажмите кнопку «Числовой формат».

Откроется диалоговое окно «Формат ячеек».

• Нажмите Процент.
• Введите 0 в десятичных разрядах.
• Нажмите ОК.
• Нажмите кнопку ОК в диалоговом окне «Параметры поля значений».
• Выберите Не показывать промежуточные итоги.

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

Недостатки неявного вычисляемого поля

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

• Они изменчивы . Это означает, что если вы отмените выбор поля, которое вы использовали для вычисляемого поля, оно будет удалено. Если вы хотите отобразить его снова, вы должны снова создать его.

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

Они изменчивы . Это означает, что если вы отмените выбор поля, которое вы использовали для вычисляемого поля, оно будет удалено. Если вы хотите отобразить его снова, вы должны снова создать его.

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

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

Создание явного вычисляемого поля

Вы можете создать явное вычисляемое поле двумя способами:

• В области расчета в таблице в модели данных. Вы уже узнали об этом в разделе — Создание вычисляемого поля в таблице.

• Из ленты PowerPivot в таблице Excel. Этот способ создания явного вычисляемого поля вы узнаете в следующем разделе.

В области расчета в таблице в модели данных. Вы уже узнали об этом в разделе — Создание вычисляемого поля в таблице.

Из ленты PowerPivot в таблице Excel. Этот способ создания явного вычисляемого поля вы узнаете в следующем разделе.

Создание явного вычисляемого поля из ленты PowerPivot

Чтобы создать явное вычисляемое поле из ленты PowerPivot, выполните следующие действия:

• Откройте вкладку POWERPIVOT на ленте в своей книге.
• Нажмите кнопку «Рассчитанные поля» в области «Расчеты».
• Нажмите Новое вычисляемое поле в раскрывающемся списке.

Откроется диалоговое окно «Вычисленное поле».

• Заполните необходимую информацию, как показано на следующем снимке экрана.

• Нажмите кнопку Проверить формулу.
• Нажмите ОК, только если в формуле нет ошибок.

Как вы можете заметить, вы можете определить категорию и формат вычисляемого поля в этом диалоговом окне. Кроме того, вы можете использовать функцию IntelliSense, чтобы понять использование функций и использовать функцию автозаполнения, чтобы легко заполнять названия функций, таблиц и столбцов. Подробнее о функции IntelliSense см. Главу « Формулы DAX» .

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