Excel Power Pivot - основы DAX

DAX (Data Analysis eXpression)language - это язык Power Pivot. DAX используется Power Pivot для моделирования данных, и его удобно использовать для самостоятельной бизнес-аналитики. DAX основан на таблицах данных и столбцах в таблицах данных. Обратите внимание, что он не основан на отдельных ячейках в таблице, как в случае с формулами и функциями в Excel.

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

Расчетный столбец

Вычисляемый столбец - это столбец в модели данных, который определяется вычислением и расширяет содержимое таблицы данных. Его можно визуализировать как новый столбец в таблице Excel, определяемой формулой.

Расширение модели данных с помощью вычисляемых столбцов

Предположим, у вас есть данные о продажах продуктов по регионам в таблицах данных, а также каталог продуктов в модели данных.

Создайте Power PivotTable с этими данными.

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

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

Как вы знаете, общая цена продукта - это цена продукта * Количество единиц, а валовая прибыль - это общая сумма - общая цена продукта.

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

• Щелкните вкладку East_Sales в представлении данных окна Power Pivot, чтобы просмотреть таблицу данных East_Sales.

• Щелкните вкладку «Дизайн» на ленте.

• Щелкните Добавить.

Столбец справа с заголовком - Добавить столбец выделен.

Тип = [Product Price] * [No. of Units] в строке формул и нажмите Enter.

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

• Дважды щелкните заголовок нового вычисляемого столбца.

• Переименуйте заголовок как TotalProductPrice.

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

• Щелкните вкладку «Дизайн» на ленте.

• Щелкните Добавить.

• Столбец справа с заголовком - Добавить столбец выделен.

• Тип = [TotalSalesAmount] − [TotaProductPrice] в строке формул.

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

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

• Дважды щелкните заголовок нового вычисляемого столбца.

• Переименуйте заголовок в Gross Profit.

Добавьте вычисляемые столбцы в North_Salesтаблицу данных аналогичным образом. Объединяя все шаги, действуйте следующим образом -

• Щелкните вкладку «Дизайн» на ленте.

• Щелкните Добавить. Столбец справа с заголовком - Добавить столбец выделен.

• Тип = [Product Price] * [No. of Units] в строке формул и нажмите Enter.

• Будет вставлен новый столбец с заголовком CalculatedColumn1 со значениями, рассчитанными по введенной вами формуле.

• Дважды щелкните заголовок нового вычисляемого столбца.

• Переименуйте заголовок как TotalProductPrice.

• Щелкните вкладку «Дизайн» на ленте.

• Щелкните Добавить. Столбец справа с заголовком - Добавить столбец выделен.

• Тип = [TotalSalesAmount] − [TotaProductPrice]в строке формул и нажмите Enter. Новый столбец с заголовкомCalculatedColumn1 вставляется со значениями, рассчитанными по введенной вами формуле.

• Дважды щелкните заголовок нового вычисляемого столбца.

• Переименуйте заголовок как Gross Profit.

Повторите указанные выше шаги для таблицы данных South Sales и таблицы данных West Sales.

У вас есть необходимые столбцы для подведения итогов валовой прибыли. Теперь создайте Power PivotTable.

Вы можете подвести итог Gross Profit это стало возможным с помощью вычисляемых столбцов в Power Pivot, и все это можно сделать всего за несколько шагов без ошибок.

Вы можете резюмировать это по регионам для продуктов, как указано ниже -

Расчетное поле

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

• Щелкните под столбцом Валовая прибыль в East_Sales таблица в окне Power Pivot.

• Тип EastProfit: = SUM ([Gross Profit]) / sum ([TotalSalesAmount]) в строке формул.

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

Вычисляемое поле EastProfit вставляется под столбцом Gross Profit.

• Щелкните правой кнопкой мыши рассчитанное поле - EastProfit.

• Выбрать Format из раскрывающегося списка.

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

• Выбрать Number в категории.

• В поле «Формат» выберите «Процент» и нажмите «ОК».

Вычисляемое поле EastProfit форматируется в процентах.

Повторите шаги, чтобы вставить следующие вычисляемые поля -

• NorthProfit в таблице данных North_Sales.

• SouthProfit в таблице данных South_Sales.

• WestProfit в таблице данных West_Sales.

Note - Вы не можете определить более одного вычисляемого поля с заданным именем.

Щелкните Power PivotTable. Вы можете видеть, что вычисленные поля появляются в таблицах.

• Выберите поля - EastProfit, NorthProfit, SouthProfit и WestProfit из таблиц в списке полей сводной таблицы.

• Расположите поля так, чтобы валовая прибыль и процентная прибыль отображались вместе. Сводная таблица Power выглядит следующим образом -

Note - The Calculate Fields были позваны Measures в более ранних версиях Excel.