Power Pivot reporting properties: Setting default aggregations

Note: This article has done its job, and will be retiring soon. To prevent "Page not found" woes, we're removing links we know about. If you've created links to this page, please remove them, and together we'll keep the web connected.

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.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.
  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.

    Power Pivot Reporting Properties:Summarize By

  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 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: Import Data into Excel, and Create a Data Model

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.