Download complete Excel example HERE.
You have used Pivot Tables before but are not a big fan because you were only able to do very simple calculations like sum, % of column or % of row. The formulas you could use inside a Pivot Table were very limited. Then PowerPivot arrived and suddenly you could do complex calculations inside PivotTables.
You recognized that Pivot Tables and Pivot Charts were now a tool that could replace hours and hours of work with a single Pivot Table.
Pivot Table Formulas Before PowerPivot
To review, Pivot Table has always had the built in measure calculations of:
Sum, Count, Min, Max and Average
As shown below, these measures can be selected by right clicking on a value in the Pivot Table field list and choosing the measure.
Release 2.0 of PowerPivot (summer of 2011) has an additional calculation, “DistinctCount”, which is useful for counting the number of distinct values in the data like customers, products of days.
Any of the above measures can be shown with no additional calculations or shown as percent of a column or row. Other additional calculations are available. To apply an additional calculation you right click on the value in the Pivot Table and select from the menu as shown below:
A complete description of built in calculations can be found at: http://office.microsoft.com/en-ca/excel-help/calculate-values-in-a-pivottable-report-HP010382405.aspx?CTT=3
This looks like a long list but you know from past experience that while the built-in calculations may work for 90% of the calculations you need there is often a ratio or comparison that cannot be calculated with the built in calculations. You can add some of these calculations in cells on the worksheet next to the Pivot Table with regular Excel formulas but you have to reenter then each time you change a filter in the Pivot Table. Other calculations are simply not possible in regular Pivot Tables.
As a result, your inclination in the past was to use a Pivot Table for simple sums and do all the difficult calculations elsewhere. This severely limited you use of Pivot Tables. It was impossible to make the Pivot Table do 100% of the calculations necessary.
New Formulas in PowerPivot
That was before PowerPivot! The new formulas available in PowerPivot Pivot Table allow you to do 100% of your calculations inside the Pivot Table. With the new DAX formulas, you can add calculated measures to your PowerPivot PivotTable like the ones below.
Warning: It takes a bit of getting used to how the new formulas work. In addition to the description below you can download an Excel workbook with the formulas in a demo Pivot Table. The Demo workbook also contains an interactive description:
Click HERE to download demo workbook.
Calculated measures you can add to a PowerPivot Pivot Table:
Equivalent to the built in sum function. All of the row and column filters in the Pivot Table are used in the calculation of Qty for each cell in the Pivot Table.
The "CALCULATE" function can be used to override row and column filter values. In this formula, a filter override of "ALL(Orders)" turns off all filters for the Pivot Table. The formula sums every quantity in the source data regardless of PivotTable row or column filters.
[Qty All Sizes]=Calculate(Sum(Orders[Quantity]),All(Orders[Size]))
This version of "CALCULATE" turns off the PivotTable filter on Size. The other row and column filters are used to determine which values are to be summed.
Orders[City]="Torino" overrides the City filter in the Pivot Table. Other row and column filters are not affected.
[Qty All Except Channel]=Calculate(Sum([Quantity]),ALLEXCEPT(Orders,Orders[Channel]))
Removing all PivotTable filters except Channel produces the total in the Channel regardless of other PivotTable filters.
[% of All]=[Qty]/[Qty All]
This formula uses calculated measures above to calculate a ratio of Quantity in each cell to total quantity.
[% of Channel]=[Qty]/[Qty All Except Channel]
This formula uses calculated measures above to calculate a ratio of Quantity in each cell total Quantity for the channel.
PowerPivot also provides many new date functions that make these calculations possible:
PREVIOUSYEAR (Date_Column [,YE_Date])
NEXTYEAR (Date_Column [,YE_Date])
DATESYTD (Date_Column [,YE_Date])
DATEADD (Date_Column, Number_of_Intervals, Interval)
DATESBETWEEN (Date_Column, Start_Date, End_Date)
DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, Interval)
PARALLELPERIOD (Date_Column, Number_of_Intervals, Interval)
TOTALMTD (Expression, Date_Column [, SetFilter])
TOTALQTD (Expression, Date_Column [, SetFilter])
TOTALYTD (Expression, Date_Column [, SetFilter] [,YE_Date])
OPENINGBALANCEMONTH (Expression, Date_Column [,SetFilter])
OPENINGBALANCEQUARTER Quarter (Expression, Date_Column [,SetFilter])
OPENINGBALANCEYEAR (Expression, Date_Column [,SetFilter] [,YE_Date])
CLOSINGBALANCEMONTH (Expression, Date_Column [,SetFilter])
CLOSINGBALANCEQUARTER (Expression, Date_Column [,SetFilter])
CLOSINGBALANCEYEAR (Expression, Date_Column [,SetFilter] [,YE_Date])
To use these formulas you need to set up a “Date Table” in your PowerPivot workbook. More information about Date Table is here: http://blogs.msdn.com/b/analysisservices/archive/2010/04/12/time-intelligence-functions-in-dax.aspx
And it will just keep getting better! Improvements to PowerPivot will be coming in force because it is such an important technology for Microsoft. PowerPivot is now in Excel, the Enterprise version of SharePoint and will soon be in the Business Intelligence portion of the Enterprise version of Microsoft’s SQL server. Because it is a key technology for Microsoft, each new release of PowerPivot will bring new and more powerful formulas.