Tip:
Highlight text to annotate it
X
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, I don’t want to add – I may want to
average or count instead. Luckily I can change the default aggregation function for each of my numeric fields.
In my data model, my Medals > Year field has a Sigma icon, indicating that it will be aggregated in some way when I add it to my report.
Here I see that Power View will always use Sum. When I add Year to a table, Power View assumes I want to add the years together.
This is not what I want. Let me show you. If I create a table to look at the number of medals awarded by gender and year, this is what I get.
All the years added together.
I can fix this by opening the dropdown for Year and selecting Do Not Summarize. Now I'm getting somewhere - my years are displaying
properly. But it’s tedious to have to do this every time I use the Year field in my report. A better fix is to change the default aggregation function
from Sum to Do Not Summarize so that each time I use Year, Power View won't add all the Years together.
In PowerPivot, I open the Medals table and place my cursor anywhere in the Year column.
In the Advanced tab, select Summarize By. Right now, Default is checked and I change this to Do not summarize.
Back in Power View, I start again from scratch creating my table.
This time, the default aggregation works perfectly.
Here's another example. I have a table that looks at the land area of participating country/regions by Olympic's year.
Power View assumes I want to add the land area together but what I really want to look at is the average and see if, as more countries
participate in the Olympic games, the average country size increases or decreases. I can change it manually each and every time in
Power View or I can use PowerPivot to change the default aggragation for Land Area from Sum to Average.
In PowerPivot I select the Medals tab and highlight the Land Area column. In the Advanced tab, click Summarize By and select Average.
Back in Power View, I apply the change and remove Land Area from the table
Now add it back and Power View automatically averages.
Non-numeric fields can also be aggregated. When I add a non-numeric field to my report as a value, Power View automatically converts it to
“Count of.” From the dropdown, I can change that to Distinct Count. I discover that 1972 was the first year that gender neutral events were
included in the Olympics. Many of the equestrian events, for example, can be contested by either male or female athletes.
Please visit office.com for more information.