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

Примечание:  Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

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

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

Вычисляемый столбец категории продукта

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

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

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

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

Столбец "Прибыль" в Power Pivot

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

Простая сводная таблица

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

Сводная таблица без полезных значений

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

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

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

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

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

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

Кнопка "Автосумма" в PowerPivot

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

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

Примечание: Эту формулу также можно записать как Общий доход: =СУММ([ОбъемПродаж]) - СУММ([Себестоимость]), но создание отдельных показателей "Общий объем продаж" и "Общая себестоимость" позволяет также использовать их в сводной таблице и в качестве аргументов в других различных формулах показателей.

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

Сводная таблица

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

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

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

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

Вычисляемый столбец доли от продаж

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

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

Сводная таблица с суммой процентов продаж для категорий товаров

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

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

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

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

Совет: Не забудьте прочитать контекст в формулах DAX. Он предоставляет разобраться уровня контекст строки и контекст фильтра, являющееся, что здесь описывается здесь.

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

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

Совет:  Явные показатели, такие как "Общий объем продаж" и "Общая себестоимость", удобно использовать не только сами по себе в сводной таблице или отчете, но и как аргументы других показателей. Это делает формулы более эффективными и простыми для понимания. Это хороший прием моделирования данных.

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

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

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

Совет:  Обязательно почитайте о функциях CALCULATE и ALLSELECTED в справочнике по DAX.

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

Правильный результат суммы доли продаж в сводной таблице

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

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

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

  • Если вы хотите новые данные отображаются на строки, СТОЛБЦЫ или фильтров в сводной таблице или на ОСИ и ЛЕГЕНДЫ, или МОЗАИКИ в визуализации Power View по, необходимо использовать вычисляемого столбца. Как и обычные столбцах вычисляемых столбцов можно использовать в качестве поля в любую область и истинности числового они могут быть объединенный в ЗНАЧЕНИЯХ слишком.

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

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

Показатели

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

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

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

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

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

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×