In this step by step PowerPivot Tutorial I will show you how to using PowerPivot change the default measure aggregation. For the purpose of this tutorial I will use PowerPivot in Excel 2013.
Other Versions of Excel:
-
PowerPivot Excel 2013 (v3)
-
This blog post has been written using PowerPivot in Excel 2013
-
PowerPivot Excel 2010 (v2) - SQL Server 2012
-
I'm actually not sure if this is available in v2
-
PowerPivot Excel 2010 (v1) - SQL Server 2008 R2
-
I suspect this may not be available in v1
Who is it for?
-
People who would like to improve user experience by changing default measure aggregation but still leaving option to change aggregation.
There are two kinds of measures in PowerPivot using PivotTable in Excel. One of them is when you use a table field (measure); that gives you an option to change aggregation in PivotTable. Second one is where you create new calculate measure (we will cover that later) and that does not allow to change aggregation in PivotTable.
Below is an example of a measure called Google Position. Whenever someone visits our website from Google we are able to capture position of our page from Google search. For instance if someone searched for PowerPivot Change Default Aggregation and our page displayed on Position 5 and was clicked by visitor then we would capture Google Position = 5.
The problem is that when I click Google Position in Pivot Table (see below). Default aggregation is used and PivotTable chose SUM which does not make sense in this case as you cannot SUM Google Position. Better choice would be Average Google Position.
We could add calculated field in PowerPivot with Average aggregation but that would mean we would NOT be able to change aggregation in PivotTable and in this case it is sometimes useful to change aggregation to Minimum or Maximum especially when I want to see Minimum Google Position per country for last week. It might be that searched keyword best position was 1 in US but 5 in UK.
In order to get best user experience and have flexibility of changing aggregation we can change Default Aggregation to Average.
To do that Go to PowerPivot Window. Select desired column. Go to Advanced tab, click Summarize by and change Default to Average
I have created new PivotTable and selected Google Position again but this time notice that PivotTable used Average of Google Position and you can see that Average of Google Position is 3.8 which makes much more sense! (than sum)
Take care
Emil
