"Difference From" custom calculation in Pivot tables

P

Peter Turton

I have been unsuccessful in using the "Difference From"
custom calculation in Pivot tables. When I use this
custom calculation (I can successfully use the % of Total
for example) I get #N/A. Also, I'm not sure what the Base
Field and Base Item boxes represent. I notice in the Base
Item box there is "Previous", "Next" and a series of
numbers which I don't understand what they represent. If
someone could straighten my out on how to use this feature
or point me in the direction of some instructions that
would take me thru this process I would really appreciate
that.
Many thanks, Peter
 
M

Max

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".
 
P

Peter Turton

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".
 
M

Max

You're welcome, Peter!
Thanks for the feedback
Glad it helped

--
Rgds
Max
xl 97
----------------------------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------------------

Max,
Thank you very much for taking the time to help me out. I realize now
Click to expand...
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.
 
Top