Results 1 to 7 of 7

Excel 2010 Calculated Columns on Pivots

This is a discussion on Excel 2010 Calculated Columns on Pivots within the Excel Questions forums, part of the Question Forums category; Hi All! My end goal is trying to do some price/volume sales analysis (Actuals vs budget) on a pivot table. ...

  1. #1
    New Member
    Join Date
    Mar 2017
    Posts
    4

    Default 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. #2
    Board Regular
    Join Date
    Dec 2013
    Posts
    697

    Default 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. #3
    New Member
    Join Date
    Mar 2017
    Posts
    4

    Default 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. #4
    Board Regular
    Join Date
    Dec 2013
    Posts
    697

    Default 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. #5
    New Member
    Join Date
    Mar 2017
    Posts
    4

    Default 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. #6
    Board Regular
    Join Date
    Dec 2013
    Posts
    697

    Default 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
    Last edited by liveinhope; Mar 21st, 2017 at 06:15 PM.

  7. #7
    New Member
    Join Date
    Mar 2017
    Posts
    4

    Default 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

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com