Excel 2010 Calculated Columns on Pivots

[theleek]

Hi All!

My end goal is trying to do some price/volume sales analysis (Actuals vs budget) on a pivot table.

The fields I have are semi transactional in a sense and are in the simplified table below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Key[/TD]
[TD]Product[/TD]
[TD]Units[/TD]
[TD]Sales[/TD]
[TD]Sales per/unit[/TD]
[/TR]
[TR]
[TD]Actuals[/TD]
[TD]Wand A[/TD]
[TD]10[/TD]
[TD]50[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Actuals[/TD]
[TD]Wand A[/TD]
[TD]12[/TD]
[TD]66[/TD]
[TD]5.5[/TD]
[/TR]
[TR]
[TD]Actuals[/TD]
[TD]Wand A[/TD]
[TD]67[/TD]
[TD]300[/TD]
[TD]4.48[/TD]
[/TR]
[TR]
[TD]Budget[/TD]
[TD]Wand A[/TD]
[TD]67[/TD]
[TD]321[/TD]
[TD]4.79[/TD]
[/TR]
[TR]
[TD]Budget[/TD]
[TD]Wand A[/TD]
[TD]100[/TD]
[TD]550[/TD]
[TD]5.5[/TD]
[/TR]
</tbody>[/TABLE]

With my limited pivot table knowledge I can pivot the above to get a table that looks like the below:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Actuals[/TD]
[TD][/TD]
[TD][/TD]
[TD]Budget[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row labels[/TD]
[TD]Units[/TD]
[TD]Sales[/TD]
[TD]Sales per unit[/TD]
[TD]Units[/TD]
[TD]Sales[/TD]
[TD]Sales per unit[/TD]
[/TR]
[TR]
[TD]Wand A[/TD]
[TD]89[/TD]
[TD]416[/TD]
[TD]15[/TD]
[TD]167[/TD]
[TD]871[/TD]
[TD]10.3[/TD]
[/TR]
</tbody>[/TABLE]

I can then create a calculated field (re-named to "Price Var") by using the "show value as" function to calculate the difference in the budgeted selling price and the actual selling price to get the below:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Actuals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Budget[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row labels[/TD]
[TD]Units[/TD]
[TD]Sales[/TD]
[TD]Sales per unit[/TD]
[TD]Price Var[/TD]
[TD]Units [/TD]
[TD]Sales[/TD]
[TD]Sales per unit[/TD]
[TD]Price Var[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]89[/TD]
[TD]416[/TD]
[TD]15[/TD]
[TD]4.7[/TD]
[TD]167[/TD]
[TD]871[/TD]
[TD]10.3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is where I get stuck! To complete the analysis I need to to that Price Var column and x by Actuals units (4.7 x 89= 417) but I can't seem to "see" the "Price Var" when trying to do another calculated cell. Once I have the price variance impact calculated I can take the total sales miss less the price impact to determine volume impact to complete the picture...that is the plan anyway!

Please help! The base data is in excel so can be amended to add more columns etc. My objective is to keep the calculation all in the pivot table as it allows for great grouping and aggregations.

I am running Windows 7 with Excel 2010

Many thanks

TheLeek

 

[liveinhope]

First problem is that the way you have totalled "sales per unit " in your pivot tables doesn't make sense

Sales per unit in your source data below is sales ($) / units.

[TABLE="width: 336"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Key[/TD]
[TD]Product[/TD]
[TD]Units[/TD]
[TD]Sales[/TD]
[TD]Sales per/unit[/TD]
[/TR]
[TR]
[TD]Actuals[/TD]
[TD]Wand A[/TD]
[TD]10[/TD]
[TD]50[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Actuals[/TD]
[TD]Wand A[/TD]
[TD]12[/TD]
[TD]66[/TD]
[TD]5.5[/TD]
[/TR]
[TR]
[TD]Actuals[/TD]
[TD]Wand A[/TD]
[TD]67[/TD]
[TD]300[/TD]
[TD]4.48[/TD]
[/TR]
[TR]
[TD]Budget[/TD]
[TD]Wand A[/TD]
[TD]67[/TD]
[TD]321[/TD]
[TD]4.79[/TD]
[/TR]
[TR]
[TD]Budget[/TD]
[TD]Wand A[/TD]
[TD]100[/TD]
[TD]550[/TD]
[TD]5.5[/TD]
[/TR]
</tbody>[/TABLE]

In the pivot table use a calculated field to work out "total of all sales for wanda" / "total units for wand a"
(actual sales of 416 , 89 units is 416/89 = 4.67 not 15 as in your original pivot table)

calculated field = =Sales /Units


[TABLE="width: 642"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Sum of Units[/TD]
[TD]Sum of Sales[/TD]
[TD]sales per unit[/TD]
[TD]Sum of Units[/TD]
[TD]Sum of Sales[/TD]
[TD]sales per unit[/TD]
[/TR]
[TR]
[TD]Wand A[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]416[/TD]
[TD="align: right"]4.67[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]871[/TD]
[TD="align: right"]5.22[/TD]
[/TR]
</tbody>[/TABLE]


Once you fix that up will move onto your original question

 

[theleek]

Thanks for the response!

Yes good point- I have tidied up the pivot (and added some products codes) in the below. Thanks for your help.

[TABLE="width: 794"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 794"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Actuals[/TD]
[TD][/TD]
[TD][/TD]
[TD]Budget[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Sum of Units[/TD]
[TD]Sum of Net Sales[/TD]
[TD]Sales per unit_[/TD]
[TD]Sum of Units[/TD]
[TD]Sum of Net Sales[/TD]
[TD]Sales per unit_[/TD]
[/TR]
[TR]
[TD]Wand A[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]416[/TD]
[TD="align: right"]4.67[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]871[/TD]
[TD="align: right"]5.22[/TD]
[/TR]
[TR]
[TD]XX2334[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]321[/TD]
[TD="align: right"]4.79[/TD]
[/TR]
[TR]
[TD]XX344[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]5.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]XX3445[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]4.48[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]XX44566[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]5.50[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]550[/TD]
[TD="align: right"]5.50[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]416[/TD]
[TD="align: right"]4.67[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]871[/TD]
[TD="align: right"]5.22[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 794"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[liveinhope]

So now you want to compare the pivot table actuals and budgets

at the moment you only have one row field (product) so I will start by doing this the "quick and dirty" way

what you do is add formula(s) outside the actual pivot table

My pivot table starts in J1 (product codes) and finishes somewhere in col P ((budget sales per unit) so I am putting my 3 "difference " formulas in columns Q,R,S

Q = Diff between actual and budget units =K4-N4
R = Diff between actual and budget sales =L4-O4
S = Diff between actual and budget sales per unit s =M4-P4

I don't quite get why you want to multiply price variance by actual units - but if you still need to do that just add another column
[TABLE="width: 834"]
<colgroup><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Actuals[/TD]
[TD][/TD]
[TD][/TD]
[TD]Budget[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Sum of Units[/TD]
[TD]Sum of Sales[/TD]
[TD]sales per unit[/TD]
[TD]Sum of Units[/TD]
[TD]Sum of Sales[/TD]
[TD]sales per unit[/TD]
[TD]diff units[/TD]
[TD]diff sales[/TD]
[TD]diff spu[/TD]
[/TR]
[TR]
[TD]Wand A[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]416[/TD]
[TD="align: right"]4.67[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]871[/TD]
[TD="align: right"]5.22[/TD]
[TD="align: right"]-78[/TD]
[TD="align: right"]-455[/TD]
[TD="align: right"]-0.54[/TD]
[/TR]
[TR]
[TD]Wand B[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]316[/TD]
[TD="align: right"]3.33[/TD]
[TD="align: right"]159[/TD]
[TD="align: right"]957[/TD]
[TD="align: right"]6.02[/TD]
[TD="align: right"]-64[/TD]
[TD="align: right"]-641[/TD]
[TD="align: right"]-2.69[/TD]
[/TR]
</tbody>[/TABLE]

 

[theleek]

Thanks but I would like the formulas to be in the pivot table rather than outside- this helps a lot with grouping and different product codes for the same product names etc.

Can the above (including the Sales price diff x actual units calculation) be achieved in the pivot table itself?

 

[liveinhope]

I can move my "difference " calculations to inside the pivot table ; but I can't do the other one you are after (Sales price diff x actual units calculation)

Follow these steps to put the existing differences in the pivot table itself

1. add SALES to the pivot table again ; you will have sum of sales showing twice .t he 2nd one will be called sum of sales2
2. right-click on "sum of sales2" and select value field settings
3. change the custom name to sales diff
4. select "show values as" tab
5. change "show values as" from normal to "difference" from and "bases from " to "next"


Repeat these steps to add units and sales-per-unit a 2nd time . when you have finished that re-arrange the order of the entries in field list as needed

[TABLE="width: 1012"]
<tbody>[TR]
[TD][/TD]
[TD]Actuals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Budget[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Sum of Units[/TD]
[TD]UNIT DIFF[/TD]
[TD]Sum of Sales[/TD]
[TD]sales diff[/TD]
[TD]sales per unit[/TD]
[TD]spu diff[/TD]
[TD]Sum of Units[/TD]
[TD]UNIT DIFF[/TD]
[TD]Sum of Sales[/TD]
[TD]sales diff[/TD]
[TD]sales per unit[/TD]
[TD]spu diff[/TD]
[/TR]
[TR]
[TD]Wand A[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]-78[/TD]
[TD="align: right"]416[/TD]
[TD="align: right"]-455[/TD]
[TD="align: right"]4.67[/TD]
[TD="align: right"]-0.54[/TD]
[TD="align: right"]167[/TD]
[TD][/TD]
[TD="align: right"]871[/TD]
[TD][/TD]
[TD="align: right"]5.22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wand B[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]-37[/TD]
[TD="align: right"]438[/TD]
[TD="align: right"]-212[/TD]
[TD="align: right"]4.76[/TD]
[TD="align: right"]-0.28[/TD]
[TD="align: right"]129[/TD]
[TD][/TD]
[TD="align: right"]650[/TD]
[TD][/TD]
[TD="align: right"]5.04[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


how important is your other calculation ? I don't have power pivot and all I know about it is that it can do things "normal" pivot tables cant do . that may be an option for you

 

[theleek]

Yip- that's where I got to. I can do "difference" calculations in a pivot but can't do basic multiply which is what I need and is the real issue.

If "Power pivot" can help then sure I will look into it but having never used this add on I am not sure if it can help me

Anybody else can help with the above?

Thanks