Manual Formula within Pivot

[kenny.fsw]

Hi ALl,


I need some help with a manual formula within a pivot please



My Source Data has Customer, Period, Qty, Sales Value and Ref Type

Within the pivot I would like to display the following data

Rows: Customer and Ref Type
Columns: Periods (2011-2012 and 2012-2013)
Values: Qty and Value

All simple so far, at the end of the columns I would like to show the Percentage difference between the period values for Qty and Value.

I do not understand how to put in a manual formula for this because the fields are the same, it is the pivot that differentiates between the two periods.

Is there a way to insert a new field and use a manual formula or somehow edit the way the Grand total works?

I have attached a test copy
TEST Variance within Pivot.xlsx

[romperstomper]

You need a calculated item using a formula like:
= IF('2011-2012'> 0,('2012-2013'-'2011-2012')/'2011-2012',0)

(See the attached workbook)

To create it, select one of the column item header cells (e.g. B4) and then on the PivotTable Tools - Options tab, choose "Fields, Items and Sets", then "Calculated Item". Choose a name for the Item (e.g. Variance) and then enter the formula as shown. You will need to edit your pivot table source range to not use entire columns.

[kenny.fsw]

Cheers for that Rory,

On the subtotals it is adding the percentages together; is there a way to get the subtotals/Grand total to follow the same formula and not just summarise with sum/count etc.?

Kenny

[romperstomper]

Not with calculated items. What you can do is add the two data fields to the table again, and choose to show the two new fields as '% difference from' and then set it to previous period. You do end up with two blank columns (since the first period has no previous one) but you can hide them if you wish.

Revised version attached.

[kenny.fsw]

Cheers Rory,

It should do the trick.