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

• joeu2004 says:

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

• Gigi says:

Ashish,

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

• lenzy says:

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

• lenzy says:

Excellent idea. Thank you for one of the best websites for useful Powerpivot application ideas.

• You are welcome. I am glad that you liked the content on my website.

• HelMen says:

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!

• W Moses says:

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.

• Hi,

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

• Alex Bush says:

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.

• Alex Bush says:

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.

• Alex Bush says:

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

• Karan says:

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,

Upload your workbook to OneDrive and share the link of the workbook here. Also, does 2012-13 mean Jan 2012 to Dec 2012? Please clarify.

• Karan says:

Hi,

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

• Hi,

Upload the workbook to OneDrive and share the link of the workbook here.

• Mehmet says:

Hello Ashish,

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?

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

• Mehmet says:

Hi Ashish,

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

• Mehmet says:

Hi,

Yes this is exactly what I am trying to do.
However I do not have enough knowledge about PowerPivot, and cannot understand how you did it.
To learn, I will look how PowerPivot is used.
King Regards

• You are welcome. You will have to learn the usage of PowerPivot’s DAX formulas.

• Mehmet says:

Tons and sales growth of Region, customer and sku…

• Wael says:

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

• Wael says:

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.

• Wael says:

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

• Wael says:

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)

• Yes. that is right. Page filters are not cross filtered. But the last sentence of your previous post said that you wanted to use slicers.

• Wael says:

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,

Yes, you can. A slicer selection can be extracted in a cell. Share the link from where I can download the workbook.

• Wael says:

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

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

• Wael says:

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

• Bryan says:

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.

• Hi,

• Bryan says:

The url when I access the uploaded workbook is:
https://onedrive.live.com/edit.aspx?cid=20530129712aa4f1&page=view&resid=20530129712AA4F1!111&parId=20530129712AA4F1!105&app=Excel&wacqt=undefined

What I’d like to see in the pivot table is the ‘Growth’ numbers that I’ve calculated outside of the pivot. Ideally, once calculated, I’d want to sort on the Growth numbers to identify customers (and their purchasing patterns) that are resulting in negative growth.

• Bryan says:

I see that my report link is very different from those posted prior. I tested the link when not logged in to OneDrive and can’t access it, so I’m not sure you’ll be able to either.

I haven’t used OneDrive before. Not sure how to get you a link to open my workbook.

• Hi,

There is no file at that link. You have to make the file “Public”. Please go through this link. You may upload the workbook to Google Drive or any other file hosting service.

• Bryan says:
• There is no data in that file. That workbook has only one worksheet with no data.

• Bryan says:

My apologies. I created the link for the wrong file. Thank you for being so patient.

• Penny says:

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

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.

• Lucy says:

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.

• Hi,

• Anup Pandey says:

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.

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.

• Hi,

I may be able to solve it with the help of the PowerPivot. Share some data, explain the business context and show the expected result.

• Kay says:

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?

• Hi,