Создание меры в сводной таблице или сводной диаграмме

  • Статья

Мера — это вычисление, которое создается с целью измерения результата по отношению к другим факторам, важным для анализа, таким как время, география, организация или характеристики продукта. Существует несколько способов создать меру, однако, в зависимости от того, как планируется ее использовать, не все подходы одинаково эффективны. То есть, если вы создаете книгу для использования в качестве модели данных в приложении для отчетов, необходимо создавать только явные меры, как описано в разделах ниже.

Создание мер для использования в моделях данных

Меры, создаваемые в книге PowerPivot, можно использовать для отчетов, создаваемых в Power View и в других приложениях для создания отчетов, совместно существующих с другими мерами или вычислениями, создаваемыми непосредственно в отчете. Чтобы мера появлялась в отчете в виде предварительно заданного вычисления, необходимо создать книгу PowerPivot в виде явной меры. Явные меры всегда создаются вручную. Они отличаются от неявных мер, создаваемых в Excel при добавлении полей в область «Значения» сводной таблицы.

Создание мер, используемых Excel

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

  • Создайте неявную меру путем перетаскивания поля из списка полей PowerPivot в область «Значения». При перетаскивании поля с числовым значением неявная мера вычисляется при помощи агрегатного выражения SUM. При перетаскивании тестового поля мера вычисляется при помощи статистического выражения COUNT. Можно изменить неявную меру, чтобы задать для вычисления другое агрегатное выражение, например MIN, MAX или DISTINCTCOUNT.

    ПримечаниеПримечание

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

  • Создание явной меры вручную при помощи кнопки Создать меру на ленте PowerPivot.

  • Создайте вручную явную меру в окне PowerPivot в Области вычислений путем ввода имени меры и ее формулы в область формул ячейки.

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

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

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

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

  1. В окне PowerPivot перейдите на вкладку Главная и выберите в группе Представления пункт Область вычислений.

  2. В таблице FactResellerSales щелкните ячейку в любом месте области вычислений.

  3. В строке формул в верхней части книги введите формулу в формате <имя_меры>:<формула>:

    Projected Sales:=SUM('FactResellerSales'[SalesAmount])*1.06

  4. Нажмите кнопку ОК, чтобы подтвердить формулу.

  5. В окне PowerPivot перейдите на вкладку Главная и выберите в группе Отчеты пункт Сводная таблица.

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

    PowerPivot создает пустую сводную таблицу в новом листе Excel и отображает Список полей PowerPivot в правой части книги.

  6. Разверните таблицу FactResellerSales, чтобы просмотреть только что созданную меру. Если у вас уже была сводная таблица в книге до создания меры, необходимо нажать кнопку «Обновить» в верхней части списка полей PowerPivot, чтобы обновить поля.

  7. В окне Excel на вкладке PowerPivot в группе Меры нажмите кнопку Создать меру.

  8. В диалоговом окне Параметры меры щелкните стрелку вниз рядом с полем Имя таблицы и выберите из раскрывающегося списка FactResellerSales.

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

  9. В поле Имя меры (все сводные таблицы) введите «Total Quantity».

  10. Имя меры должно быть уникальным в книге; нельзя также использовать имена столбцов в книге.

  11. В текстовом поле Формула поместите курсор за знаком равенства (=) и введите следующую формулу:

    SUM(FactResellerSales[OrderQuantity])

  12. Нажмите кнопку ОК.

Обе созданные меры сохранятся в таблице исходных данных, однако они могут использоваться в любой сводной таблице или сводной диаграмме. Меры отображаются в списке Список полей PowerPivot и доступны для всех пользователей книги.

Пример. Создание явной меры, в которой используется пользовательский агрегат

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

  • DateTime[CalendarYear]

  • ProductCategory[ProductCategoryName]

  • ResellerSales_USD[SalesAmount_USD]

В примере используется сводная таблица, в которой применяется CalendarYear в качестве метки строки и ProductCategoryName в качестве метки столбца; SalesAmount_USD используется в формуле меры. В примере приводится ответ на вопрос: какой процент от общих продаж в 2005–2008 гг. составляют продажи по каждому году и продукту? Пример демонстрирует, в частности, какой процент от общих продаж составляют продажи велосипедов в 2007 г. Для ответа на этот вопрос используется следующая формула меры.

=SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

Формула составляется следующим образом.

  1. Числитель, SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD]), является суммой значений в валюте ResellerSales_USD[SalesAmount_USD] для текущей ячейки в сводной таблице. Поскольку учитывается контекст типов CalendarYear и ProductCategoryName, это значение будет отличаться для каждого сочетания года и категории продукта. Например, общее количество велосипедов, проданных в 2003 г., отличается от общего количества аксессуаров, проданных в 2008 г.

  2. Для знаменателя сначала указывается таблица ResellerSales_USD, и используется функция ALL для удаления всего контекста таблицы. Благодаря этому указанное значение всегда остается одним и тем же для каждого сочетания года и категории товаров. Знаменатель всегда показывает общие продажи за 2005–2008 гг.

  3. Затем вызывается функция SUMX для суммирования значений в столбце ResellerSales_USD[SalesAmount_USD]. Иными словами, вычисляется сумма ResellerSales_USD[SalesAmount_USD] по продажам всех торговых посредников.

ПримечаниеПримечание

В Windows Vista и Windows 7 функции в окне PowerPivot доступны на ленте, обсуждаемой в данном разделе. В Windows XP функции доступны в наборе меню. Если в Windows XP необходимо просмотреть, каким образом команды меню связаны с командами ленты, см. раздел Пользовательский интерфейс PowerPivot в Windows XP.

Создание меры, в которой применяется пользовательский агрегат

  1. В окне PowerPivot перейдите на вкладку Главная и выберите в группе Отчеты пункт Сводная таблица.

  2. Убедитесь, что в диалоговом окне Создание сводной таблицы выбран параметр Создать лист, и нажмите кнопку ОК.

    PowerPivot создает пустую сводную таблицу в новом листе Excel и отображает Список полей PowerPivot в правой части книги.

  3. В окне Excel используйте Список полей PowerPivot для добавления столбцов в сводную таблицу.

    1. Найдите таблицу DateTime и перетащите столбец CalendarYear в область Row Labels сводной таблицы.

    2. Найдите таблицу ProductCategory и перетащите столбец ProductCategoryName в область Row Labels сводной таблицы.

  4. В окне Excel на вкладке PowerPivot в группе Меры нажмите кнопку Создать меру.

  5. В диалоговом окне Параметры меры щелкните стрелку вниз рядом с полем Имя таблицы и выберите из раскрывающегося списка ResellerSales_USD.

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

  6. В поле Имя меры (все сводные таблицы) введите «AllResSalesRatio».

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

  7. В поле Пользовательское имя (эта сводная таблица) введите «Отношение продаж по всем торговым посредникам».

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

  8. В текстовом поле Формула поместите курсор после знака равенства (=).

  9. Введите SUMX, а затем открывающую скобку.

    =SUMX(

    По мере ввода подсказка под текстовым полем Формула укажет, что для функции SUMX необходимо два аргумента: первым аргументом является таблица или выражение, возвращающее таблицу, а вторым аргументом — выражение, которое предоставляет числа для сложения.

    Введите «Res», выберите из списка ResellerSales_USD и нажмите клавишу TAB.

    Имя столбца вставляется в формулу следующим образом:

    =SUMX(ResellerSales_USD

  10. Введите запятую.

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

  11. Введите первые несколько букв имени таблицы, содержащей столбец, который нужно включить. Для этого примера введите «Res» и выберите из списка столбец ResellerSales_USD[SalesAmount_USD].

  12. Нажмите клавишу TAB, чтобы вставить имя столбца в формулу, и добавьте закрывающую скобку, как показано далее:

    =SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])

  13. Введите косую черту, затем введите или скопируйте и вставьте в диалоговое окно Параметры меры следующий код:

    SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

    Обратите внимание, что функция ALL вложена в функцию SUMX. Конечная формула имеет следующий вид:

    =SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

  14. Нажмите кнопку Проверить формулу.

    Будет выполнена проверка синтаксиса и ссылок в формуле. Устраните ошибки (если они найдены), например добавьте отсутствующую скобку или запятую.

  15. Нажмите кнопку ОК.

    После этого мера заполняет сводную таблицу значениями для каждого сочетания календарного года и категории продукта.

  16. Форматирование таблицы.

    1. Выберите данные в сводной таблице, включая строку Общий итог.

    2. На вкладке Главная в группе Число щелкните один раз кнопку процентов (%), затем дважды нажмите кнопку увеличения числа десятичных разрядов (<- .0 .00).

    Конечная таблица должна выглядеть, как показано ниже. Теперь видно процент от общего объема продаж по каждому сочетанию товара и года. Например, продажи велосипедов в 2007 г. составляли 31,71% от всех продаж в 2005–2008 гг.

All Reseller Sales

Column Labels

 

 

 

 

Метки строк

Accessories

Bikes

Clothing

Components

Общий итог

2005

0.02%

9.10%

0.04%

0.75%

9.91%

2006

0.11%

24.71%

0.60%

4.48%

29.90%

2007

0.36%

31.71%

1.07%

6.79%

39.93%

2008

0.20%

16.95%

0.48%

2.63%

20.26%

Общий итог

0.70%

82.47%

2.18%

14.65%

100.00%

См. также

Справочник

Диалоговое окно ключевого показателя эффективности

Основные понятия

Меры в PowerPivot

Окно PowerPivot: Область вычисления

Другие ресурсы

Общие сведения об отчетах, диаграммах и сводных таблицах