Computing growth % inside a pivot table

{ 66 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%!!!!!!

Leave a Comment

Your email address will not be published.

*

  • 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.)

  • 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

  • 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!

  • 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.

  • 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.

  • 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.

  • 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.

      • Hi,

        Thanks for your reply. I shall be sending the link shortly to your inbox.

        Please note that 2012-13 means April 2012 to March 2013 and so on. Our Financial begins in April of current year to March of next year.

        Regards,
        Karan

  • 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

  • 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

  • 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 😛 )
    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

    • Hi,

      You are welcome. Nice try at your end. In addition to using only one formula, I have also give the end user, the leeway to select any month and base year (slicers) to start comparison from.

  • 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

    • Hi,

      Slicers are by default cross filtered. If you select Beirut in the Caza slicer, then all the relevant areas of the Beirut Caza will light up in the Areas slicer (other areas should grey out).

  • 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

    • 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.

  • 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

  • Your solution works where the “year” is a data point. My data set is transactional (daily) data that is being grouped into year/month reported values. When I try to create the calculated field, I get an error stating that I “cannot add a calculate item to a grouped field”.

    Is there a solution for calculating the growth of one year over the other when grouped? Ideally, when the year is expanded to display the months, the growth formula would display January (year 1) vs. January (year 2) etc.

    Thank you.

  • Dear Ashis Mathur,
    Would you like help me to create % growth flexible for slicer by period such as: when I click on period 1 & 2 => growth is comparison between 1 & 2; when I click on period 2 & 3 => growth is comparison between 2 & 3

    Please see attached file in below link for data

    https://1drv.ms/x/s!ArDtP0dKjfuuaaB7JKGDfWJZgb8

    Thank you so much

    • Hi,

      Will you ever be selecting non consecutive period such as 1 and 3? Also, it will be a lot better if you can share a column of dates and tell me how you derived the periods from that column of dates.

  • Hi Ashish,

    How do you add à cagr calculation to a pivot table calculated field.
    I have tried the traditional cage formula and it doesn’t work.

  • Ret Sales
    Ret Sales
    Met Sales
    Met Sales
    Det Sales
    Det Sales
    Fet Sales
    Fet Sales
    Get Sales
    Get Sales

    What if i have 5000+ Account name like Fet Sales and so on.

    As per your statement you had mention one point.

    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.

    How can i do for 5000+ data for the above statement in once at a time.

  • Hello Ashish,

    Please can you advise how I would show sales growth OR decline in a pivot table when i have sales displayed by customer, quarter and rank ( of total customer sales)?

    For example; Customer name starts in cell B9, Qtr1 sales in C9, Qtr1 sales ranking ( total customer sales) in D9, Qtr2 sales in E9, Qtr1 sales ranking ( total customer sales) in F9 and so on?

  • How can one effectively utilize a pivot table to calculate and analyze growth percentages in computing, and what key steps or formulas are involved in presenting this information within the context of data analysis?