Назначение вычисляемых столбцов и полей

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

Сначала рассмотрим пример, где мы используем вычисляемый столбец, чтобы добавить новое текстовое значение для каждой строки в таблице "Товар". Каждая строка в таблице "Товар" содержит различную информацию о продаваемых нами товарах. В ней есть столбцы "Название товара", "Цвет", "Размер", "Цена посредника" и т. д. У нас также есть другая связанная таблица "Категория товара", которая включает столбец "НазваниеКатегорииТовара". Мы хотим, чтобы для каждого товара в таблице "Товар" было указано название его категории из таблицы "Категория товара". Для этого в таблице "Товар" можно создать вычисляемый столбец "Категория товара":

Наша новая формула Категории продукта использует функцию RELATED DAX для получения значений из столбца ProductCategoryName в связанной таблице Product Category, а затем вводит эти значения для каждого продукта (каждая строка) в таблице Product.

Это хороший пример того, как можно использовать вычисляемый столбец для добавления фиксированного значения для каждой строки, которое можно затем использовать в области СТРОКИ, СТОЛБЦЫ или ФИЛЬТРЫ сводной таблицы или в отчете Power View.

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

Мы можем создать вычисляемый столбец, который рассчитывает сумма прибыли для каждой строки путем вычитания значений в столбце "Себестоимость" продаж из значений в столбце "ОбъемПродаж":

Теперь создадим сводную таблицу и перетащим поле "Категория товара" в область СТОЛБЦЫ, а наше новое поле "Прибыль" — в область ЗНАЧЕНИЯ (столбец в таблице PowerPivot — это поле в списке полей сводной таблицы). В результате будет создан неявный показатель с именем Сумма по полю Прибыль. Это агрегат значений в столбце прибыли для каждой из категорий товаров. Результат выглядит так:

В этом случае поле "Прибыль" имеет смысл добавлять только в область ЗНАЧЕНИЯ. Если поместить его в область СТОЛБЦОВ, сводная таблица будет выглядеть следующим образом:

Поле "Прибыль" не позволяет получить полезные сведения, если оно находится в области ФИЛЬТРЫ, СТРОКИ или СТОЛБЦЫ. Оно имеет смысл только как агрегированное значение в области ЗНАЧЕНИЯ.

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

Это лучше делать с помощью явного показателя.

Пока мы оставим вычисляемый столбец "Прибыль" в таблице "Продажи", а также столбцы "Категория товаров" в области СТОЛБЦЫ и "Прибыль" в области ЗНАЧЕНИЯ сводной таблицы, чтоб сравнить результаты.

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

Во-первых, в таблице "Продажи" выберем столбец "ОбъемПродаж" и нажмем кнопку "Автосумма" для создания явного показателя Сумма по полю ОбъемПродаж. Явным показателем называется показатель, который создается в области вычислений таблицы в Power Pivot. Сделаем то же самое для столбца "Себестоимость". Переименуем столбцы в Общий объем продажи Общая себестоимость, чтобы их было проще идентифицировать.

Затем создадим другой показатель с помощью этой формулы:

Общий доход: = [ Общий объем продаж]-[Общая себестоимость]

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

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

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

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

В этом примере необходимо вычислить сумму продаж в процентах от общего объема продаж. Мы создали вычисляемый столбец % продаж в таблице "Продажи":

В формуле указано: Для каждой строки в таблице "Продажи" нужно разделить значение в столбце "ОбъемПродаж" на сумму значений в столбце "ОбъемПродаж".

Если создать сводную таблицу, добавить столбец "Категория товара" в область СТОЛБЦЫ, а новый столбец % продаж —в область ЗНАЧЕНИЯ, можно получить сумму процентов продаж для каждой из категорий товаров.

Хорошо. Пока все идет замечательно. Но давайте добавим срез. Мы добавляем "Календарный год", а затем выбираем "Год". В этом случае выберем 2007. Вот что мы получим.

На первый взгляд значения опять же кажутся правильными. Но сумма процентов должна равняться 100 %, так как мы хотим знать процент от суммы продаж для каждой из категорий товаров в 2007 г. В чем ошибка?

Столбец "% продаж" вычисляет процент для каждой строки, то есть значение в столбце "ОбъемПродаж", поделенное на общую сумму всех значений в столбце "ОбъемПродаж". Значения в вычисляемом столбце являются фиксированными. Они дают неизменяемый результат для каждой строки в таблице. Когда мы добавили столбец % продаж в сводную таблицу, для него была вычислена сумма всех значений в столбце "ОбъемПродаж". Сумма всех значений в столбце "% продаж" всегда будет равна 100 %.

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

Помните показатель "Общий объем продаж", который мы создали ранее (тот, который просто суммирует значения в столбце "ОбъемПродаж")? Мы использовали его в качестве аргумента для показателя "Общая прибыль", а теперь мы используем его в качестве аргумента для нового вычисляемого поля.

Создадим новый показатель с помощью следующей формулы:

% от суммы продаж: =([Общий объем продаж]) / CALCULATE([Общий объем продаж], ALLSELECTED())

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

Теперь при добавлении нового столбца % от суммы продаж в сводную таблицу мы получим следующее:

Так намного лучше. Теперь значение % от суммы продаж для каждой категории товаров вычисляется как процент от суммы продаж в 2007 г. Если в срезе "Календарный год" выбрать другой год или несколько лет, мы получим новые процентные значения для категорий товаров, но общий итог по-прежнему будет составлять 100%. Также можно добавить другие срезы и фильтры. Показатель "% от суммы продаж" всегда будет выдавать процент от суммы продаж независимо от того, какие срезы и фильтры применены. При использовании показателей результат всегда рассчитывается в зависимости от контекста, определяемого полями в областях СТРОКИ и СТОЛБЦЫ, а также всеми примененными фильтрами и срезами. В этом заключается преимущество показателей.

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

Вычисляемые столбцы

• Если вы хотите, чтобы новые данные отображались в строках, столбцах или фильтрах в сводной таблице, а также в axis, LEGEND или TILE BY в визуализации Power View, необходимо использовать вычисляемый столбец. Как и обычные столбцы данных, вычисляемые столбцы можно использовать в качестве поля в любой области, а если они числовые, их можно агрегировать в значениях.

• Используйте их, если вы хотите, чтобы новые данные были фиксированными значениями для строки. Например, допустим, что у вас есть таблица дат со столбцом дата и вы хотите добавить столбец, содержащий номер месяца. Вы можете создать вычисляемый столбец, который вычисляет номер месяца из даты в столбце "Дата". Например, =MONTH('Дата'[Дата]).

• Если вы хотите добавить текстовое значение для каждой строки в таблице, используйте вычисляемый столбец. Поля, содержащие текстовые значения, никогда нельзя вычислять в области ЗНАЧЕНИЯ. Например =FORMAT('Дата'[Дата];"ммммм") дает название месяца для каждой даты в столбце "Дата" таблицы "ДАТА".

Показатели

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

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

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

Помните, что нет ничего неправильного в том, чтобы создать вычисляемый столбец, как мы сделали со столбцом "Прибыль", а затем выполнить с ним статистическое вычисление в сводной таблице или отчете. Это хороший и простой способ создавать собственные вычисления. Когда вы хорошо изучите эти две мощные функции Power Pivot, вы сможете создавать максимально эффективные и точные модели данных. Надеемся, что вы узнали что-то полезное для себя. Существуют и другие замечательные ресурсы, которые могут вам помочь. Вот лишь несколько примеров: Контекст в формулах DAX, Агрегатные функции в Power Pivot и Центр ресурсов по DAX. И, хотя это немного более сложный и ориентированный на специалистов по бухгалтерскому учету и финансам, пример "Моделирование и анализ данных о прибыли и убытках с помощью Microsoft Power Pivot в Excel " содержит отличные примеры моделирования данных и формул.