Excel Power Pivot — Основы DAX

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

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

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

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

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

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

Создайте сводную таблицу Power с этими данными.

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

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

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

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

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

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

• Нажмите Добавить.

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

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

Нажмите Добавить.

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

Тип = [Цена продукта] * [№ из единиц] в строке формул и нажмите Enter .

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

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

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

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

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

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

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

• Нажмите Добавить.

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

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

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

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

Нажмите Добавить.

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

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

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

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

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

• Переименуйте заголовок как Валовая прибыль.

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

Переименуйте заголовок как Валовая прибыль.

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

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

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

• Тип = [Цена продукта] * [№ из единиц] в строке формул и нажмите Enter.

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

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

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

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

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

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

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

• Переименуйте заголовок как Валовая прибыль .

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

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

Тип = [Цена продукта] * [№ из единиц] в строке формул и нажмите Enter.

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

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

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

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

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

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

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

Переименуйте заголовок как Валовая прибыль .

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

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

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

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

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

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

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

• Введите EastProfit: = SUM ([Валовая прибыль]) / сумма ([TotalSalesAmount]) в строке формул.

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

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

Введите EastProfit: = SUM ([Валовая прибыль]) / сумма ([TotalSalesAmount]) в строке формул.

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

Рассчитанное поле EastProfit вставляется под столбцом «Валовая прибыль».

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

• Выберите Формат из выпадающего списка.

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

Выберите Формат из выпадающего списка.

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

• Выберите номер под категорией.

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

Выберите номер под категорией.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечание . Поля вычисления в предыдущих версиях Excel назывались « Меры» .