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

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

Наиболее распространенные агрегаты, например с помощью функции AVERAGE, COUNT, DISTINCTCOUNT, MAX, MIN или SUM , можно создать в меру автоматически с помощью автосуммы. Другие типы агрегатов, такие как AVERAGEX, COUNTX, COUNTROWS или SUMX, возвращают таблицу и требуют формулы, созданной с помощью выражений анализа данных (DAX).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Если вы в общих чертах представляете, как нужно сгруппировать данные и с какими значениями вы хотите работать, можно выбрать построение сводной таблицы или создание вычислений в самой таблице. Power Pivot расширяет и улучшает собственные возможности Excel для создания агрегатов, таких как суммы, подсчеты или средние значения. Пользовательские агрегаты можно создавать в Power Pivot либо в окне 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 формула интерпретируется как "Получить значение в поле Amount для каждой строки таблицы Sales, а затем сложить эти отдельные значения. Power Pivot оценивает каждую строку, по которой выполняется агрегирование, и вычисляет одно скалярное значение для каждой строки, а затем выполняет агрегирование этих значений. Поэтому результат формулы может быть разным, если к таблице применялись фильтры или если значения вычислялись на основе других агрегатов, где могли использоваться фильтры. Дополнительные сведения см. в статье Контекст в формулах DAX.

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

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

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

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

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