Calculate variance within Power Pivot Table

Pivot Table is a great tool to group data into major categories for reporting. You can place and compare the numbers by categories between 2 periods (e.g. This Year and Last Year) side by side within the pivot table. But the existing Pivot Table is not effective in calculating the variance between the 2 periods. The workaround is to copy the entire Pivot Table into another part of the worksheet as values and compute the variance using worksheets formulas. This work around may be simple but it is very

tedious if you have multiple Pivot Table reports to prepare. If you are currently experiencing this problem and using Excel 2010, I have great news for you. You can now add Power Pivot, a brand new function by Microsoft, to your Pivot Table and have the variance calculated for you within the Pivot Table. No more work around is required. Here is how it is done.

Convert the range (of data) into a table

Select one of the cells in the range. Go to the Insert tab and select the table icon (second one from the left).

In the pop-up window, double check the range and make sure that the option box next to “My Table has header label” is checked. Click OK.

Give your table a name

Click anywhere within the table , go to the “Design” Tab and replace the name currently found in the box located on the left of the ribbon with “Sales_data” .

Add the data into Power Pivot

Go to the PowerPivot Tab and click on the icon “create linked table”.

Create a table of dates for linking to Sales_data.

In another worksheet, create a column of running dates (Column A) , starting with a date earlier on the first date of your data and ending with a date on or later than the last date of your Sales data. Name the table as “Calendar” and the column as “Date”.

Add the Dates table into Power Pivot using the same “Create Link” method used by Sales Data.

Link the Sales_data table and the Dates table together

Go to the Power Pivot Tab and select Power Pivot window.

In the Power Pivot Window, click on the “Diagram view” icon. Join the dates (Invoice Dates and Dates) from both tables together.

Create Pivot table

In the PowerPivot tab, click on the Pivot Table icon. When you are brought back to Excel, choose a cell you wish to place your Pivot Table on.

Create Measures (aka FoRmulas) for the Pivot Table

In Excel, go to the Power Pivot Tab and click on the Measures icon.

In the measure settings window, choose the table “Sales_data” as the location to store the measure. Give the name “Sales” to the measure and enter the formula for the measure in the formula box. The formula is

=SUM(Sales_data[Amount]).

Format the Sales number. I use the currency format with zero decimal place for the measure shown in the diagram below. Click OK. The measure will be inserted into the Summation value box of the Pivot Table.

Create another measure for the “LY Sales”. The formula is =CALCULATE(Sales_data[Sales],SAMEPERIODLASTYEAR(Calendar[Date]))

Create another measure for the “LY Sales”. The formula is

=CALCULATE(Sales_data[Sales],SAMEPERIODLASTYEAR(Calendar[Date]))

Create a third measure “Variance” to compute the difference between this year sales and last year sales.

=if(isblank(Sales_data[LY Sales]),BLANK(),Sales_data[Sales]/Sales_data[LY Sales]-1)

=if(isblank(Sales_data[LY Sales]),BLANK(),Sales_data[Sales]/Sales_data[LY Sales]-1)

Drop the division into the row label box of the Pivot Table and you will have the variance for both years 2010 and 2011.