Excel 2010 Calculated Columns on Pivots

theleek

New Member
Joined
Mar 20, 2017
Messages
4
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:

KeyProductUnitsSalesSales per/unit
ActualsWand A10505
ActualsWand A12665.5
ActualsWand A673004.48
BudgetWand A673214.79
BudgetWand A1005505.5

<tbody>
</tbody>

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

ActualsBudget
Row labelsUnitsSalesSales per unitUnitsSalesSales per unit
Wand A894161516787110.3

<tbody>
</tbody>

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:

ActualsBudget
Row labelsUnitsSalesSales per unitPrice VarUnits SalesSales per unitPrice Var
89416154.716787110.3

<tbody>
</tbody>

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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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.

KeyProductUnitsSalesSales per/unit
ActualsWand A10505
ActualsWand A12665.5
ActualsWand A673004.48
BudgetWand A673214.79
BudgetWand A1005505.5

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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


Row LabelsSum of UnitsSum of Salessales per unitSum of UnitsSum of Salessales per unit
Wand A894164.671678715.22

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>


Once you fix that up will move onto your original question
 
Upvote 0
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.

Column Labels
ActualsBudget
Row LabelsSum of UnitsSum of Net SalesSales per unit_Sum of UnitsSum of Net SalesSales per unit_
Wand A894164.671678715.22
XX23340.00673214.79
XX34410505.000.00
XX3445673004.480.00
XX4456612665.501005505.50
Grand Total894164.671678715.22

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>


<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
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
Column Labels
ActualsBudget
Row LabelsSum of UnitsSum of Salessales per unitSum of UnitsSum of Salessales per unitdiff unitsdiff salesdiff spu
Wand A894164.671678715.22-78-455-0.54
Wand B953163.331599576.02-64-641-2.69

<colgroup><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>
 
Upvote 0
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?
 
Upvote 0
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

ActualsBudget
Row LabelsSum of UnitsUNIT DIFFSum of Salessales diffsales per unitspu diffSum of UnitsUNIT DIFFSum of Salessales diffsales per unitspu diff
Wand A89-78416-4554.67-0.541678715.22
Wand B92-37438-2124.76-0.281296505.04

<tbody>
</tbody>


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
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top