Computing growth % inside a pivot table

{12 Comments}

Having created a Pivot Table from a Base_Data sheet, one may want to compute the Account wise and Growth % of 2010 over 2009.

There are two ways one can go about answering the question above:

Solution A – For Excel 2010 and higher versions – This solution is for those using the PowerPivot MS Excel add-in for Excel 2010 and higher versions.

Solution B – For all versions of MS Excel – This solution will work in all versions of MS Excel but for those using Excel 2010 and higher versions, the PowerPivot solution would be far more efficient.

The steps for creating a pivot table under Solution B are:

1. Select cell C4 on the sheet "Pivot Table"
2. Go to Pivot Table Tools > Options > Fields, Items and Sets > Calculate Item.  In Excel 2007, this will be Pivot Table Tools > Options > Formulas > Calculated Item
3. In the Name box, type Growth
4. In the Formula bar, enter =('2010' /'2009' )-1 and press OK
5. Select the Growth column in the pivot table and press Ctrl+Shift+5 to convert the decimals to percentages

The problem with the above process is that the Growth % in the Grand Total column (cell D10) will be incorrect i.e. -31.93%.  Unfortunately, this is the simple summation of the Growth % above.  Ideally, this should be 1.77%and it should be computed as:

=(323,332-317,718)/317,718

First of all, this problem is happening because cell D10 is at the intersection of two formulas - one is Excel's default SUM function for the Grand Total column and the other is the calculated field formula created above.  We need to prioritise the application of formula on this particular cell i.e. the Growth % formula written above should take precedence over the default SUM function.  To accomplish this, try the following steps:

1. Right click on cell A10 (Grand Total) and select Remove Grand Total
2. Select cell A9 and go to Pivot Table Tools > Options > Fields, Items and Sets > Calculate Item.  In Excel 2007, this will be Pivot Table Tools > Options > Formulas > Calculated Item
3. In the Name box, type Total
4. In the Formula bar, enter =Ret Sales + Met Sales + Det Sales + Fet Sales + Get Sales.  You can do so by double clicking on the individual items available
5. After performing the steps above, the problem would be the same as we had earlier i.e. the Growth % in cell D10 will show -31.93%
6. To prioritise calculations in cell D10, try this:
a. Select any one cell in the pivot table and go to Pivot Table Tools > Options > Fields, Items and Sets > Solve Order.  In Excel 2007, this will be Pivot Table Tools > Options > Formulas > Solve Order
b. Select the first item and click on Move Down
c. Click on Close

The Growth % in cell D10 should now appear as 1.77%.

Change in growth from -31.93% to 1.77%!!!!!!

RSS 12 Comments…

 Share your views
  1. Two minor aesthetic comments:

    1. In the second Step 3, I would retain the (user's) original title, namely Grand Total.

    2. After following the second set of Steps 1 through 6, the color of the Grand Total line is not the same as before (blue background).

    I remedied that by simply selecting the A10:D10 in the pivot table and selecting the appropriate blue shade from the Fill Color icon in the Home > Font tab.

    As a pivot table neophite, I don't know if that additional step is correct, necessary and worthy of mention. But I thought I'd bring it to your attention for consideration.

    (Perhaps there is a way to preserve the original backgroun color by doing something other than Remove Grand Total in Step 1.)

  2. Ashish,

    I truly believe this is remarkable. I just learned something new. Thank you for sharing your knowledge.

  3. Thank You. Excellent Post!

    Would your process work if the table was as follows: (where one column only has 2009 Amount and one column only has 2010 Amount).

    Account 2009 Amount 2010 Amount
    Ret Sales 130,000 140000
    Ret Sales 200,000 210000
    Met Sales 53,000 63000
    Met Sales 65,000 75000
    Det Sales 77,000 87000
    Det Sales 25,999 35999
    Fet Sales 48,793 58793
    Fet Sales 22,333 32333
    Get Sales 8,925 18925
    Get Sales 10,000 20000

    How would the equations change?
    ------------Revenues:=sum(Dummy[Amount])
    ------------Growth (%):=(CALCULATE([Revenues],Dummy[Year]=2010)-CALCULATE([Revenues],Dummy[Year]=2009))/CALCULATE([Revenues],Dummy[Year]=2009)

    The table from your example is below:

    Account Amount Year
    Ret Sales 130,000 2009
    Ret Sales 200,000 2010
    Met Sales 53,000 2009
    Met Sales 65,000 2010
    Det Sales 77,000 2009
    Det Sales 25,999 2010
    Fet Sales 48,793 2009
    Fet Sales 22,333 2010
    Get Sales 8,925 2009
    Get Sales 10,000 2010

  4. Thank you so much for this extremely useful post. This is something we've been trying to find a solution to for ages in our office!

  5. Hi,

    How would this work if the years are going to be dynamic. i.e If I have page filter for country. Country A has data for year 2009 to 2012 and another country B has 2009 to 2014.

Leave a Comment

Your email address will not be published.

*