Calculating growth in pivot tables

Calculating growth in pivot tables

Didn't find your answer?

I have a pivot table based on data like:
type year revenue
a 2006 100
b 2006 100
a 2007 120
b 2007 110

And I want a pivot table like:
Sum of revenue year
type 2006 2007 Growth
a 100 120 20%
b 100 110 10%

But I can't see how to do this in the pivot table - using a formula doesn't seem to allow me to talk about the series within "year"

Any suggestions?

Thanks

Tony
Tony Rush

Replies (4)

Please login or register to join the discussion.

avatar
By Richard Willis
05th Jun 2007 09:06

Not a problem
Hi Tony

If you do exactly what you have shown, i.e. put the type in the rows and the years in the columns, then you can add formulae in the columns to the right to calculate the growth year on year and/or cumulatively.

Thanks (0)
avatar
By tonyrush
05th Jun 2007 11:25

Hmmm
So I can certainly put in formulae in the cells and calculate anything I need, but how can I get a column in the pivot table to do this? Using the "formula" option on the table itself, I can write formulae using "year", "revenue" etc, but not 2006 or 2007.

I'd like to do it in the table itself as it should be a lot less work when I change the number of rows or columns.

Thanks

Tony

Thanks (0)
avatar
By RichardSchollar
05th Jun 2007 13:33

Use an extra Revenue Data field
Hi Tony

You can do this without needing to create a Pivot Formula:

1. When you create the pivot table, you want to have row fields of Type and Year, and 2 data fields of Revenue (ie drag the Revenue button onto the Data area twice).

2. Drag the Year button (once it is in the row area of the PT) onto the cell that will have "Total Sum Of Revenues" - or something like that - this will put the years in columns (don't place the Year button as a Column field initially - you want it as a Row field).

3. Drag the Data button onto the "Total ...." cell as well. You should now have two value columns under each year date against each type.

4. Select any cell in the second Revenue data area, right-clik your mouse and choose Field Settings. Hit the Option button on the dialog that opens, and choose from the "Show data as" drop down "% Difference From".

5. Choose a Base Field of Year and a Base Item of 2006. Finally click OK.


This will result in an empty column for one 2006 data field (as it isn't any different from itself), but you can always narrow the column width or simply hide it if required. You can rename the % column to whatever you require (eg % Growth).

Hope this helps!

Richard

Thanks (0)
avatar
By tonyrush
05th Jun 2007 14:15

Thanks
Richard

Many thanks - that works. I also experimented with Calculated Items (rather than fields) and that does what I need too.

Cheers

T

Thanks (0)