Агрегатные функции в PowerPivot

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

Наиболее распространенные агрегаты, например с использованием функций СРЗНАЧ,СЧЁТ,РАЗЛИЧИМЧИСЛО,MAX, МИН или СУММ , могут создаваться в мере автоматически с помощью функции автосуммирования. Другие типы агрегатов, такие как AVERAGEX, COUNTX, COUNTROWS или SUMX, возвращают таблицу и требуют формулы, созданной с использованием выражений анализа данных (DAX).

Общие сведения об агрегатах в Power Pivot

Выбор групп для агрегата

При агрегатной обработке данных они группируются по таким атрибутам, как продукт, цена, регион или дата, а затем определяется формула, работающая для всех данных в группе. Например, если создаются итоговые показатели за год, то это агрегат. Если создается соотношение этого года с предыдущим годом и данные представляются в виде процентов, то это другой тип агрегата.

Метод группировки данных определяется поставленным бизнес-вопросом. Например, агрегаты могут ответить на следующие вопросы.

Число Сколько транзакций было за месяц?

Средние значения Каковы были средние продажи в этом месяце по продавцам?

Минимальное и максимальное значения Какие районы продаж вошли в пятерку лучших по объему проданных единиц?

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

Если поступившие данные не содержат значений, которые можно использовать для группирования (таких как категория товара или географический регион, где расположен магазин), можно создать группы данных путем добавления категорий. При создании групп в Excel необходимо вручную ввести или выделить нужные группы из числа столбцов в рабочем листе. Однако в реляционных системах многие иерархии (например, категории продуктов) хранятся не в той таблице, где хранятся факты или значения. Обычно таблица категорий связана с данными фактов с использованием какого-либо ключа. Например, предположим, что в данных содержатся идентификаторы продуктов, но не их имена или категории. Чтобы добавить категорию в неструктурированный рабочий лист Excel, потребовалось бы скопировать столбец, содержащий названия категорий. С помощью Power Pivot можно импортировать таблицу категорий продуктов в модель данных, создать связь между таблицей с числовыми данными и списком категорий продуктов, а затем использовать категории для группировки данных. Дополнительные сведения см. в статье Создание отношения между таблицами.

Выбор функции для агрегата

После определения и добавления групп необходимо решить, какие математические функции следует использовать для агрегирования. Часто слово "агрегат" используется в качестве синонима математических или статистических операций, применяемых в агрегатах, таких как суммирование, определение средних значений, определение минимума или подсчет. Однако Power Pivot позволяет создавать пользовательские формулы для агрегирования в дополнение к стандартным агрегатам, которые есть в Power Pivot и Excel.

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

Отфильтрованные счетчики Сколько транзакций было совершено за месяц без учета периода обслуживания?

Отношения с использованием средних значений с течением времени Каков был процентный рост или снижение продаж по сравнению с аналогичным периодом прошлого года?

Сгруппированные минимальные и максимальные значения Какие районы продаж заняли первое место в каждой категории продуктов или в каждой рекламной акции?

Добавление агрегатов к формулам и сводным таблицам

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

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

Добавление группирований в сводную таблицу

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

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

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

Работа с группированиями в формуле

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

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

Дополнительные сведения о создании формул с подстановками см. в статье Подстановка в формулах PowerPivot.

Использование фильтров в агрегатах

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

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

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

Дополнительные сведения см. в статье Фильтрация данных в формулах.

Сравнение агрегатных функций Excel с агрегатными функциями DAX

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

Стандартные агрегатные функции

Функция	Использование
AVERAGE	Возвращает среднее арифметическое всех чисел из столбца.
AVERAGEA	Функция возвращает среднее (арифметическое) всех значений в столбце. Обрабатывает текстовые и нечисловые значения.
COUNT	Функция подсчитывает количество числовых значений в столбце.
COUNTA	Функция подсчитывает количество непустых значений в столбце.
MAX	Возвращает наибольшее числовое значение из столбца.
MAXX	Функция возвращает наибольшее значение из набора выражений, вычисленных в таблице.
MIN	Возвращает наименьшее числовое значение в столбце.
MINX	Функция возвращает наименьшее значение из набора выражений, вычисленных в таблице.
SUM	Функция добавляет все числа в столбец.

Агрегатные функции DAX

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

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

Функция	Использование
AVERAGEX	Функция определяет среднее арифметическое для набора выражений, вычисленных в таблице.
COUNTAX	Функция подсчитывает набор выражений, вычисленных в таблице.
COUNTBLANK	Функция подсчитывает количество пустых значений в столбце.
COUNTX	Функция подсчитывает общее количество строк в таблице.
COUNTROWS	Функция подсчитывает количество строк, возвращенных вложенной табличной функцией, такой как функция фильтра.
SUMX	Функция возвращает сумму набора выражений, вычисленных в таблице.

Различия между агрегатными функциями DAX и Excel

Хотя эти функции имеют те же имена, что и их аналоги в Excel, они используют модуль аналитики Power Pivot в памяти и были переписаны для работы с таблицами и столбцами. Формулу DAX нельзя использовать в книге Excel и наоборот. Их можно использовать только в окне Power Pivot и в сводных таблицах, основанных на данных Power Pivot. Кроме того, хотя функции имеют одинаковые названия, поведение может немного отличаться. Дополнительные сведения см. в разделах, посвященных отдельным функциям.

Способ вычисления столбцов в статистическом выражении также отличается от способа обработки статистических выражений в Excel. Проиллюстрировать это поможет пример.

Предположим, требуется получить сумму значений в столбце Amount таблицы Sales, для чего создается следующая формула:

=SUM('Sales'[Amount])

В самом простом случае функция возвращает значения из одного неотфильтрованного столбца, и результат будет таким же, как в приложении Excel, в котором всегда просто суммируются значения в столбце Amount. Однако в Power Pivot формула интерпретируется следующим образом: "Получить значение в сумме для каждой строки таблицы "Продажи", а затем сложить эти отдельные значения. Power Pivot оценивает каждую строку, для которой выполняется агрегирование, и вычисляет одно скалярное значение для каждой строки, а затем выполняет агрегирование этих значений. Поэтому результат формулы может быть разным, если к таблице применялись фильтры или если значения вычислялись на основе других агрегатов, где могли использоваться фильтры. Дополнительные сведения см. в статье Контекст в формулах DAX.

Функции логики операций со временем DAX

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

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

Функция	Использование
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR	Функция вычисляет значение на конечную дату календаря данного периода.
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR	Функция вычисляет значение на конечную дату календаря периода, предшествующего данному.
TOTALMTD TOTALYTD TOTALQTD	Функция вычисляет значение для интервала, начинающегося в первый день периода и заканчивающегося последней датой в указанном столбце дат.

Другие функции в разделе «Логика операций со временем» (Функции логики операций со временем) — это функции, которые могут использоваться для извлечения дат или пользовательских диапазонов дат для использования в агрегате. Например, с помощью функции DATESINPERIOD можно получить диапазон дат и использовать этот набор дат в качестве аргумента другой функции для вычисления пользовательского агрегата только по этим датам.