Thanks:  0
Likes:  0

# Thread: Excel 2010 Calculated Columns on Pivots

1. ## Excel 2010 Calculated Columns on Pivots

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:

 Key Product Units Sales Sales per/unit Actuals Wand A 10 50 5 Actuals Wand A 12 66 5.5 Actuals Wand A 67 300 4.48 Budget Wand A 67 321 4.79 Budget Wand A 100 550 5.5

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

 Actuals Budget Row labels Units Sales Sales per unit Units Sales Sales per unit Wand A 89 416 15 167 871 10.3

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:

 Actuals Budget Row labels Units Sales Sales per unit Price Var Units Sales Sales per unit Price Var 89 416 15 4.7 167 871 10.3

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

2. ## Re: Excel 2010 Calculated Columns on Pivots

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.

 Key Product Units Sales Sales per/unit Actuals Wand A 10 50 5 Actuals Wand A 12 66 5.5 Actuals Wand A 67 300 4.48 Budget Wand A 67 321 4.79 Budget Wand A 100 550 5.5

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 Labels Sum of Units Sum of Sales sales per unit Sum of Units Sum of Sales sales per unit Wand A 89 416 4.67 167 871 5.22

Once you fix that up will move onto your original question

3. ## Re: Excel 2010 Calculated Columns on Pivots

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 Actuals Budget Row Labels Sum of Units Sum of Net Sales Sales per unit_ Sum of Units Sum of Net Sales Sales per unit_ Wand A 89 416 4.67 167 871 5.22 XX2334 0.00 67 321 4.79 XX344 10 50 5.00 0.00 XX3445 67 300 4.48 0.00 XX44566 12 66 5.50 100 550 5.50 Grand Total 89 416 4.67 167 871 5.22

4. ## Re: Excel 2010 Calculated Columns on Pivots

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 Actuals Budget Row Labels Sum of Units Sum of Sales sales per unit Sum of Units Sum of Sales sales per unit diff units diff sales diff spu Wand A 89 416 4.67 167 871 5.22 -78 -455 -0.54 Wand B 95 316 3.33 159 957 6.02 -64 -641 -2.69

5. ## Re: Excel 2010 Calculated Columns on Pivots

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?

6. ## Re: Excel 2010 Calculated Columns on Pivots

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

 Actuals Budget Row Labels Sum of Units UNIT DIFF Sum of Sales sales diff sales per unit spu diff Sum of Units UNIT DIFF Sum of Sales sales diff sales per unit spu diff Wand A 89 -78 416 -455 4.67 -0.54 167 871 5.22 Wand B 92 -37 438 -212 4.76 -0.28 129 650 5.04

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

7. ## Re: Excel 2010 Calculated Columns on Pivots

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•