Computing growth % inside a pivot table

{44 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 44 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.

  6. […] Computing growth % inside a pivot table 2. Compute Pro rata growth rate within a Pivot […]

  7. What if your sales data was -60k for 2009 and -20k for 2010. The growth would show negative when in fact it is positive. I'm dealing with Gross Profit data that has this and I can't figure out how to take that into consideration (two negatives). Any help would be greatly appreciated.

  8. If you'll notice on row 7 (upon opening), 2014 GP has actually improved by $40k. It is still showing a -5.35% change however. I've used calculated fields for all columns except for the Sales $ figures and GP $ figures.

  9. Wow, thank you so much Sir!!! I can't thank you enough! This was such a thorn in my side.

  10. Hi,

    I need to compare growth of 3 years. I am using excel 2013. What if my fields are labelled 2012-13, 2013-14 and 2014-15?

    Kindly suggest.

  11. Hello Ashish,

    Thank you very much for your sharing, your website is very helpful for me.
    For my example, I have tons and sales for values. On the other hand, I have regions, customers and SKUs. I am trying to calculate in pivot table for region, customers and SKU for both of tons and sales. Is that possible?
    Please see the file in below link.

    Thank you in advance for your help.

    https://www.wetransfer.com/downloads/a837d7f36b32895969b24adeef1c1e0320150107205625/a63c1c35a1a849a1ff9b15cc02f9f95a20150107205625/c09e2e

    • Hi,

      I do not understand your question. You already have yearly totals (columns F,G,L,M). In the column labels, just set the order of the Fields as Year, Month and Values. Also, click on any Pivot Table cell and go to Pivot Table Tools > Design > Layout > Grand Totals > On for Rows and Columns.

      • Hi Ashish,

        Thank you for your reply.
        I have added new columns to my data manually to show what I want to do.
        I want to see this new columns inside of pivot table.
        Is it possible to do this with a calculated field or powerpivot options.
        Please see my example in below link, then you will understand what I want to do.

        Thank you very much for your effort.

        http://we.tl/NyWPpm75lf

  12. Tons and sales growth of Region, customer and sku...

  13. Hi
    Thanks for your tips, they are really great.
    However, i have a rather unique problem. I want to calculate the growth for the average price per region based on a specific fixed date (e.g. first half H1 2010) eg. growth of a specific region in H2 2010, then in H1 2011, then in H2 2011 etc.. all relative to H1 2010.
    Then I will have to plot them in a chart. (each region in a chart)
    Is it doable using pivot table? I attached an example, the original data contains thousands of records.
    http://1drv.ms/1IQDVHC

  14. Hi,
    Wow, thank you i will take a look on it (the formula in particular, i will need some time to grasp them all). Now all i have to do is find a way to plot them and put them in a nice interface/table/dashboard.. (why in hell pivot tables are ugly :P )
    Yesterday, i worked on something and i think i was close enough (based on your tips here) but i had to create several growth formula (ignore the normalization to 100) but you did it in just one! you can have a look on it
    http://1drv.ms/1KSb6yC
    Again thank you very much

  15. Hi
    Thank you, yeah i noticed your nice "touch".
    However, i have another question. (the example sheet i uploaded doesn't have all the data). I know i can use page filter in pivot tables or slicers in order to select the caza then region then area. Is there a way to make them linked or cascaded? For example if i select Beirut as a Caza, i want to only show the relevant regions and then when selecting a region, to show only the relevant area.
    Probably i can do it using drop lists, data validation and references, but is there a way using slicers?
    Thanks

  16. Slicers are cross-filtered but not page filters.
    If you have a look at this excel file
    http://c3412576.r76.cf0.rackcdn.com/04_18_12a.xlsx
    and if i choose the year 2008 (in page filter) i can still choose the month Feb even though i shouldn't.
    Guess i am stuck with slicers (the list of regions is quite large over 50)

  17. Hi
    Also, i cannot refer to a slicer selection in other formula right? If a select Beirut in the Caza Slicer, there is no way i can use that selection in other formula if i wanted.
    Thanks

  18. Hi
    Ah sorry i wasn't clear enough. I could use slicers if i can extract the selection to a cell.
    http://1drv.ms/1AtZtpc
    Thanks again for your help

    • Hi,

      Try this

      1. Select A1:C322 on sheet1 and convert it to a Table (already done in the file that you uploaded)
      2. Select A1:C322 on sheet1 and assign it a name - say territory
      3. Select A1:C322 and go to PowerPivot > Add to Data Model > OK
      4. In the PowerPivot window, select Pivot Table > Pivot Table
      5. Create a slicer for Caza and select Beirut in that slicer
      6. In cell F1, enter this formula

      =CUBESET("ThisWorkbookDataModel",Slicer_Caza1,"Caza")

      slicer_caza1 id the name of the Caza slicer. You can obtain this name by right clicking on the slicer and selecting Slicer Settings > Name to use in formulas.

      7. In cell F2, enter this formula

      =CUBERANKEDMEMBER("ThisWorkbookDataModel",F$1,ROW()-1)

      8. If you want a single cell formula, then enter this formula in cell F2

      =CUBERANKEDMEMBER("ThisWorkbookDataModel",CUBESET("ThisWorkbookDataModel",Slicer_Caza1,"Caza"),ROW()-1)

      You may refer to my solution in the slicer extraction worksheet of this workbook.

      Hope this helps.

  19. Hi Thank you again and again
    I never used the cubeset formula before, time to learn it then :). I think it is the same formula used when you use OLAP tools to convert Report filters to formula i guess

Leave a Comment

Your email address will not be published.

*