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