Power Pivot reporting properties: Setting default aggregations

There are many different way to aggregate a number. Instead of making you choose each time you add a value field to a report, Power View performs the default aggregation. The default aggregation is Sum. But for some fields, you don't want to add – you may want to average or count instead. Luckily you can change the default aggregation function for each of your numeric fields.

If it's a field you don't plan on using often, changing the aggregation each time you use it in your report might be your best option. But for fields you plan on using over and over, it will save you time to change the default aggregation.

1. Open Power Pivot > Manage.

2. Select the table that contains the value you want to aggregate.

3. Place your cursor anywhere in that column, and open the Advanced tab.

4. Select the aggregation you'd like to set as the default for this value.

   

5. Back in Power View, add this value to your report and notice that the default aggregation has changed to match the setting you made in Power Pivot.

Tip:  Often you'll have values that are represented by numbers but what you really want is a count of how many times that particular number appears in your report. For example, years are numbers but rarely will you want to add up the years. In cases like this, change the default aggregation from SUM to Do Not Summarize.

See Also

Power Pivot reporting properties for Power View

Power View and Power Pivot videos

Power Pivot: Powerful data analysis and data modeling in Excel

Power View: Explore, visualize, and present your data

Aggregations in Power Pivot

Tutorial: PivotTable data analysis using a Data Model in Excel