Aggregations in Power Pivot

Choosing Groups for Aggregation

When you aggregate data, you group data by attributes such as product, price, region, or date and then define a formula that works on all the data in the group. For example, when you create a total for a year, you are creating an aggregation. If you then create a ratio of this year over the previous year and present those as percentages, it is a different type of aggregation.

The decision of how to group the data is driven by the business question. For example, aggregations can answer the following questions:

Counts   How many transactions were there in a month?

Averages    What were the mean sales in this month, by salesperson?

Minimum and maximum values    Which sales districts were the top five in terms of units sold?

To create a calculation that answers these questions, you must have detailed data that contains the numbers to count or sum, and that numeric data must be related in some way to the groups that you will use to organize the results.

If the data does not already contain values that you can use for grouping, such as a product category or the name of the geographical region where the store is located, you might want to introduce groups to your data by adding categories. When you build groups in Excel, you must manually type or select the groups you want to use from among the columns in your worksheet. However, in a relational system, hierarchies such as categories for products are often stored in a different table than the fact or value table. Usually the category table is linked to the fact data by some kind of key. For example, suppose you find that your data contains product IDs, but not the names of products or their categories. To add the category to a flat Excel worksheet, you would have to copy in the column that contained the category names. With Power Pivot, you can import the product category table to your data model, create a relationship between the table with the number data and the product category list, and then use the categories to group data. For more information, see Create a relationship between tables.

Choosing a Function for Aggregation

After you have identified and added the groupings to use, you must decide which mathematical functions to use for aggregation. Often the word aggregation is used as a synonym for the mathematical or statistical operations that are used in aggregations, such as sums, averages, minimum, or counts. However, Power Pivot enables you to create custom formulas for aggregation, in addition to the standard aggregations found in both Power Pivot and Excel.

For example, given the same set of values and groupings that were used in the preceding examples, you could create custom aggregations that answer the following questions:

Filtered counts   How many transactions were there in a month, excluding the end-of-month maintenance window?

Ratios using averages over time    What was the percentage growth or decline in sales compared to the same period last year?

Grouped minimum and maximum values    Which sales districts were ranked top for each product category, or for each sales promotion?

Adding Groupings to a PivotTable

When you design a PivotTable, you drag fields that represent groupings, categories, or hierarchies, to the columns and rows section of the PivotTable to group the data. You then drag fields that contain numeric values into the values area so that they can be counted, averaged, or summed.

If you add categories to a PivotTable but the category data is not related to the fact data, you might get an error or peculiar results. Usually Power Pivot will try to correct the problem, by automatically detecting and suggesting relationships. For more information, see Work with Relationships in PivotTables.

You can also drag fields into Slicers, to select certain groups of data for viewing. Slicers let you interactively group, sort, and filter the results in a PivotTable.

Working with Groupings in a Formula

You can also use groupings and categories to aggregate data that is stored in tables by creating relationships between tables, then creating formulas that leverage those relationships to look up related values.

In other words, if you want to create a formula that groups values by a category, you would first use a relationship to connect the table containing the detail data and the tables containing the categories, and then build the formula.

For more information about how to create formulas that use lookups, see Lookups in Power Pivot Formulas.

Using Filters in Aggregations

A new feature in Power Pivot is the ability to apply filters to columns and tables of data, not only in the user interface and within a PivotTable or chart, but also in the very formulas that you use to calculate aggregations. Filters can be used in formulas both in calculated columns and in s.

For example, in the new DAX aggregation functions, instead of specifying values over which to sum or count, you can specify an entire table as the argument. If you did not apply any filters to that table, the aggregation function would work against all the values in the specified column of the table. However, in DAX you can create either a dynamic or static filter on the table, so that the aggregation operates against a different subset of data depending on the filter condition and the current context.

By combining conditions and filters in formulas you can create aggregations that change depending on the values supplied in formulas, or that change depending on the selection of rows headings and column headings in a PivotTable.

For more information, see Filter Data in Formulas.

Standard Aggregation Functions

DAX Aggregation Functions

DAX includes aggregation functions that let you specify a table over which the aggregation is to be performed. Therefore, instead of just adding or averaging the values in a column, these functions let you create an expression that dynamically defines the data to aggregate.

The following table lists the aggregations functions that are available in DAX.

Differences between DAX and Excel Aggregation Functions

Although these functions have the same names as their Excel counterparts, they utilize Power Pivot’s in-memory analytics engine and have been rewritten to work with tables and columns. You cannot use a DAX formula in an Excel workbook, and vice versa. They can only be used in the Power Pivot window and in PivotTables that are based on Power Pivot data. Also, although the functions have identical names, the behavior may be slightly different. For more information, see the individual function reference topics.

The way that columns are evaluated in an aggregation is also different from the way that Excel handles aggregations. An example may help illustrate.

Suppose you want to get a sum of the values in the Amount column in the table Sales, so you create the following formula:

=SUM('Sales'[Amount])

In the simplest case, the function gets the values from a single unfiltered column, and the result is the same as in Excel, which always just adds up the values in the column, Amount. However, in Power Pivot, the formula is interpreted as "Get the value in Amount for each row of the Sales table, and then add up those individual values. Power Pivot evaluates each row over which the aggregation is performed and calculates a single scalar value for each row, and then performs an aggregation on those values. Therefore, the result of a formula can be different if filters have been applied to a table, or if the values are calculated based on other aggregations that might be filtered. For more information, see Context in DAX Formulas.