Calculated field with pivot columns

I've got a worksheet that looks like this
YEAR |  ITEMNO | REVENUE
-----|---------|-----------------
2010 | number1 | $5
2010 | number1 | $6
2010 | number2 | $10
2011 | number1 | $6
2011 | number2 | $9
2011 | number2 | $6
2012 | number1 | $8
2012 | number2 | $10

Open in new window

I put it into an excel pivot table so the results look like this
ITEMNO  | 2010 |  2011 | 2012 
--------|------|-------|------
number1 | $11  |   $6  |  $8
number2 | $10  |  $15  |  $10

Open in new window

I would like to add a calculated column to the right to calculate the difference between the revenue for of the years like this
ITEMNO  | 2010 |  2011 | 2012 | 10-11 Diff | 11-12 Diff  
--------|------|-------|------|------------|------------
number1 | $11  |   $6  |  $8  |    $-5     |   $2
number2 | $10  |  $15  |  $10 |    $5      |   $-5

Open in new window

Is this something that is possible? If so, how can I do this?
LVL 2
David11011Asked:
Who is Participating?
 
nutschConnect With a Mentor Commented:
Add Revenue a second time as a data field in your pivot
Right-click that second revenue column and pick Show value as...Difference From
Base field: Year / Base Item: Previous
Update the column header

You're done. Sadly there's no way I know to hide the first empty column, but you can hide the column until the next pivot refresh.

Thomas
0
 
helpfinderIT ConsultantCommented:
do you mean something like that on my example?
sample.xlsx
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Something like attached.

Calculated Year on Year movement using a helper column in the data. Against each line the formula gives the same result for each combo of year and account so then displayed in the pivot as Average.

=SUMIFS($C$2:$C$9,$A$2:$A$9,$A2,$B$2:$B$9,$B2)-SUMIFS($C$2:$C$9,$A$2:$A$9,$A2-1,$B$2:$B$9,$B2)

Haven't found a way of putting Variance columns to right hand side.

Solution from helpfinder could be used, ie adding additional formula alongside the pivot but these won't necessarily recalculate correctly by having hardcoded cell references in the formula. You could do similar using GETPIVOTDATA function.

Thanks
Rob H
Pivot-Variance.xlsx
0
 
David11011Author Commented:
Thank you for your replies this will work just fine for what I am wanting to do. It would be nice to put all the values to the right hand side but I this will work fine. Thank you so much for your help!!!! Exactly what I needed!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.