Max,
Thank you very much for taking the time to help me out. I realize now that I was trying to apply the "Difference From" custom calculation for the difference between tow columns whereas it is meant to calculate the difference between the base item in a column to the other items in the column. By experimenting with what you showed me I could apply it to two columns provided there is no more than two columns of data in the source data so probably the "Calculated Formula" is the way to go for the difference between two columns.
Many thanks, Peter
----- Max wrote: -----
Let's try a simple example to understand what goes on when we use the
options 'Difference From' & '% Difference From'.
a. Input* the table below in say A1:C10
(*or try copy > paste special > text)
------------------------------
Region Product Sales
West Apple 30
West Apple 50
West Banana 50
West Cherry 20
North Cherry 50
North Apple 50
North Apple 100
East Banana 60
South Apple 50
-------------------------------
b. Create the pivot table (PT) as per normal
In step 3 of the PT wizard:
Drag 'Region' & 'Product' to Row area
Drag 'Sales' 3 times to Data area
Leave the first one "Sum of Sales" as it is
Double-click on "Sum of Sales2"
Click 'Options'
Under 'Show data as:'
Select 'Difference From'
Select 'Region' under Base field
Select 'West' under Base item
Click OK
Double-click on "Sum of Sales3"
Click 'Options'
Under 'Show data as:'
Select '% Difference From'
Select 'Region' under Base field
Select 'West' under Base item
Click OK
Click Finish to exit the PT wizard
----------------------
Go to the PT sheet
Click outside of the PT, say on cell F1, and insert a row before row1
Drag 'Data' (in C2) up / to the right (to D1)
The PT should look like below:
("visualize" without possible awful text-wrap)
..................Data
Region..Product..Sum of Sales..Sum of Sales2..Sum of Sales3
East......Banana....60................10............20.00%
East Total..........60...............-90...........-60.00%
North.....Cherry....50................30...........150.00%
...........Apple....150................70............87.50%
North Total........200................50............33.33%
South Apple.........50...............-30...........-37.50%
South Total.........50..............-100...........-66.67%
West......Cherry....20
...........Apple.....80
...........Banana....50
West Total.........150
Grand Total........460
---------------------------------
Let's examine some sample lines of what's shown
under 'Sum of Sales2' & 'Sum of Sales3'
For the 1st row: East Banana 60 10 20.00%
The '10' sales units under 'Sum of Sales2'
is the difference between West's Banana of '50' & East's '60'
viz: 60-50=10
The '20.00%' under 'Sum of Sales3' is the % difference in the sales units
between West's Banana of '50' & East's '60'
viz: (60-50)/50=0.2 or 20%
-----------------
Likewise, for the 2nd row: East Total 60 -90 -60.00%
The '-90' sales units under 'Sum of Sales2' is the difference
between West's Total of '150' & East's '60'
viz: 60-150=-90
The '-60.00%' under 'Sum of Sales3' is the % difference in the sales units
between West's Total of '150' & East's '60'
viz: (60-150)/150=-0.6 or -60%
---------------
And so on for the other rows. All comparisons in regions (other than West)
will be calculated against the Region-West which was our selection made for
the base field - base item.
Trust the above will give you some understanding
& hang on the use of "Difference from" & "% difference from".
