Создание меры

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

Основные сведения о мерах

Мера — это формула, которая создается специально для использования в сводной таблице или сводной диаграмме, использующей данные PowerPivot. Меры могут быть основаны на стандартных агрегатных функциях, например COUNT или SUM, либо на пользовательских формулах на языке выражений анализа данных (DAX). Мера используется в области Значения сводной таблицы. Если необходимо разместить вычисленные результаты в другой области сводной таблицы, то для этого следует использовать вычисляемый столбец (Создание вычисляемого столбца).

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

Создание и изменение мер

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

После добавления сводной таблицы или сводной диаграммы в книгу PowerPivot откройте диалоговое окно Параметры меры, чтобы добавить меру, содержащую формулу. В формуле определяется сумма, среднее или другое вычисление, в котором используются столбцы и таблицы в окне PowerPivot. Можно создавать стандартные агрегаты точно таким же образом, как и в Excel, — путем перетаскивания полей в область Значения и выбора одного из методов агрегирования: COUNT, SUM, AVERAGE, MIN или MAX. Пользовательские агрегаты рассматриваются в следующем разделе.

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

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

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

  • DateTime[CalendarYear]

  • ProductCategory[ProductCategoryName]

  • ResellerSales_USD[SalesAmount_USD]

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

=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 г., отличается от общего количества аксессуаров, проданных в 2004 г.

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

  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. Выберите данные в сводной таблице, включая строку Grand Total.

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

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

All Reseller Sales

Column Labels

 

 

 

 

Row Labels

Accessories

Bikes

Clothing

Components

Grand Total

2001

0.02%

9.10%

0.04%

0.75%

9.91%

2002

0.11%

24.71%

0.60%

4.48%

29.90%

2003

0.36%

31.71%

1.07%

6.79%

39.93%

2004

0.20%

16.95%

0.48%

2.63%

20.26%

Grand Total

0.70%

82.47%

2.18%

14.65%

100.00%

Изменение существующей меры

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

Просмотр и изменение существующей меры

  1. В окне Excel щелкните любое место сводной таблицы или сводной диаграммы, чтобы открыть Список полей PowerPivot.

  2. В Списке полей PowerPivot найдите таблицу, которая содержит созданную меру.

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

    В этом примере щелкните правой кнопкой мыши меру SumAmtByReseller и выберите Изменить формулу.

  3. В диалоговом окне Параметры меры измените формулу.

    Также можно изменить имя меры или пользовательское имя и связанную таблицу.

  • Last updated on