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

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 for sharing your thoughts.

Ashish,

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

Thank you. You are most welcome.

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

Hi,

Thank you. You may refer to my solution in this workbook.

Hope this helps.

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.

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!

You are most welcome. Thank you for the feedback.

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,

This should be possible to resolve. Please upload your workbook to OneDrive and share the link of the workbook here.

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

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.

Hi,

This should be possible to do in a Pivot Table. Upload your workbook to OneDrive and share the download link here.

The download link is:

https://onedrive.live.com/redir?resid=A7075E230B7720B7%21113

Hi,

Please refer to my solution in the attached workbook. I have made a correction for the Calculated Field titled as "% Change GP".

Hope this helps.

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.

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

You are most welcome.

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.

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

Hi,

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

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,

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

Hope this helps.

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.

Thank you for your time.

King Regards

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

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

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,

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

Hope this helps.

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

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.

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.

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.

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

You are welcome.

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,

Upload the workbook to OneDrive and share the download link here. Also, share your expected solution in that workbook.

Sorry for the delay. I had to create a redacted spreadsheet.

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.

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.

Hopefully this works:

https://onedrive.live.com/redir?resid=20530129712AA4F1!106&authkey=!AIrhpg1Ow0TiICo&ithint=file%2cxlsx

There is no data in that file. That workbook has only one worksheet with no data.

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

This should work:

https://onedrive.live.com/redir?resid=20530129712AA4F1!111&authkey=!AG2wssEFOx-4bB4&ithint=file%2cxlsx

Hi,

You may refer to my PowerPivot solution in this workbook.

Hope this helps.