Important: In Excel for Microsoft 365 and Excel 2021, Power View is removed on October 12, 2021. As an alternative, you can use the interactive visual experience provided by Power BI Desktop, which you can download for free. You can also easily Import Excel workbooks into Power BI Desktop.
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 
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.
-
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.
-
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.
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.
-
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.
-
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.
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.
-
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.
-
Click Do not summarize.
Each item may now have multiple rows, each with a different amount.
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.
