Change a sum to an average or other aggregate in Power View

In the Field List in Power View, some number fields have a Sigma ∑ symbol next to them. They are aggregates, meaning they will be summed or averaged, for example.

Say you have a chart that sums the sales data for different regions, but you’d rather have the average.

  • In the Field List click the arrow next to the numeric value and click Average.

 Note    If a field has a calculator icon Calculator icon in Power View next to it, then it is a calculated field, and you can’t change the aggregate. For example, if it’s a sum, it can only be a sum.

In this article


Aggregate a numeric field

For example in Power View, you might have a Rating field, with ratings from 1 to 5. You add it to a table in a view and think that therefore you should be able to convert the table to a chart, but all the chart icons are grayed and disabled. You notice the field has no Sigma ∑ symbol next to it, and that there is a row in the table for every rating of every item.

  1. In the Table fields or Values box in the layout section of the field list, click the drop-down arrow next to a numeric field.

Note that Do not summarize is checked.

  1. Check one of the aggregation options: Sum, Average, Minimum, Maximum, or Count.

Let’s say you click Average. Here are things that have changed:

  • You now see just one row for each item.
  • The numeric field is an average of the values.
  • In the Values box in the layout section of the field list, the field name has a Sigma ∑ next to it.
  • The chart icons in the Visualizations Gallery are enabled.

 Note    The field in the fields section of the field list is still a non- aggregated field, but the field in the table in the view is an aggregate.

Top of Page Top of Page

Convert a text (non-numeric) field to an aggregated field

You can convert a text field to an aggregate so you can count it, and then display the count in charts. For example, let’s say you want to know how many products are in each product subcategory. You add the Category and Product fields to a table in the view. You see a long list of the products in each category. All the chart types are grayed on the Design tab because the matrix has no aggregates.

  1. In the layout (lower) section of the field list, click the drop-down arrow next to a non-numeric field.

Note that Do not summarize is checked.

  1. Click Count.

Add a text field to a visualization as an aggregate

You can also make a field a counted field as you add it to a visualization, rather than adding and then converting it. This can speed up performance, because Power View does not have to fetch all the items in the field.

  • For a matrix or chart, drag a field from the fields (upper) section of the field list and drop it in the Values box.
  • In the fields section of the field list, click the drop-down arrow next to a non-numeric field.
    • For a matrix or chart, click Add to Values.
    • For a table, click Add to Table as Count.

Doing any of these automatically adds the field as a counted field.

Duplicates and blanks in a field

When you set Power View to count the values in a field, by default it counts all the rows that contain data: It counts duplicate values, but not blanks. You can set it to instead count only unique (distinct) values, including blanks.

  • Click the arrow next to the field in the layout section of the field list, and click Count (Non Blank) or Count (Distinct).

 Note    The column label in a visualization for either kind of counted field is the same: Count of <Field Name>. You can tell whether it counts distinct or non-blank values by clicking the dropdown arrow next to the field in the Values box for a matrix or chart or the Fields list for a table.

Top of Page Top of Page

Convert an aggregate to a non-aggregated field

There may be times when you do want to see every value of a field, rather than aggregating it by summing or averaging, for example.

 Note    You can only change an aggregate to a non-aggregated field in a flat table. The option doesn’t exist in any other visualization.

  1. In the Table fields or Values box in the layout section of the field list, click the drop-down arrow next to an aggregate.

Note that an aggregate, such as Sum, is checked.

  1. Click Do not summarize.

Each item may now have multiple rows, each with a different amount.

Top of Page Top of Page

Power View changes how it handles integers

By default, Power View for SharePoint 2010 and SQL Server 2012 aggregated decimal numbers, but treated integers as categories rather than aggregating them. A data model designer either in Power Pivot or in SQL Server Data Tools could set defaults for integers, but this was the default behavior. In Power View in SharePoint 2013 and SQL Server 2012 SP1, Power View aggregates both decimal numbers and integers by default. A data model designer can still specify other default behavior, but that is the default.

Integer behavior when you upgrade a Power View in SharePoint 2010 report to Power View in SharePoint 2013

When you upgrade a Power View in SharePoint 2010 report to Power View in SharePoint 2013, the default behavior of any integers will change, if they are default fields and the data model creator didn’t set a default behavior.

Integers in Power View for SharePoint 2010

In Power View for SharePoint 2010, if a table contains integers:

Category Item Quantity
Drink Apple juice 12
Food Bread 7
Drink Tea 5
Food Crackers 46

When you create a table in Power View with Category and Price fields, it doesn’t add the quantities because the numbers are integers:

Category Quantity
Drink 12
Food 7
Drink 5
Food 46

You can make Power View sum or otherwise aggregate the numbers by clicking the arrow in the Field List and selecting Sum, Average, Count, or another aggregate.

Integers in Power View in Excel 2013 and SharePoint 2013

In Power View in Excel 2013 and SharePoint 2013, when you add a number field, whether decimal or integer, the default is to sum the values. So in the above example of the integers, the table in Power View is:

Category Quantity
Drink 17
Food 53

Again, you can change that default and make Power View not summarize, but the default behavior has changed.

Top of Page Top of Page

 
 
Applies to:
Excel 2013, Power BI for Office 365, Power View in Excel 2013, Power View in SharePoint Server 2013