Pivot Table: Variance formula
 

Within a pivot table, how can I calculate a VARIANCE between 2 columns,
rather than the default "Grand Total" as shown below (i.e., I need to see a
variance of 8 on the Dog/Male row, rather than the "total" of 12) ?

Animal Gender Born Sold Grand Total
Dog Male 10 2 12
Female 5 2 7
Cat Male 1 1 2
Female 3 2 5
Hamster Male 6 3 9
Female 2 1 3

Pivot Table: Variance formula
 

Hi

Right click on PTtable optionsDeselect Grand Total by row.

Create a calculated field for your value. From the dropdown on the PT
ToolbarFormulasCalculated FieldName Variance
Formula = Born - Sold


--
Regards
Roger Govier



"Grunt_13" wrote in message
...
Within a pivot table, how can I calculate a VARIANCE between 2 columns,
rather than the default "Grand Total" as shown below (i.e., I need to see
a
variance of 8 on the Dog/Male row, rather than the "total" of 12) ?

Animal Gender Born Sold Grand Total
Dog Male 10 2 12
Female 5 2 7
Cat Male 1 1 2
Female 3 2 5
Hamster Male 6 3 9
Female 2 1 3

Pivot Table: Variance formula
 

Add another copy of the Born/Sold field to the data area.
Right-click on a cell in that column, and click on Field Settings
Click Options, and from the dropdown list choose Difference From
As the Base Field, select the Born/Sold field
As the Base Item, select Sold
Click OK

Grunt_13 wrote:
Within a pivot table, how can I calculate a VARIANCE between 2 columns,
rather than the default "Grand Total" as shown below (i.e., I need to see a
variance of 8 on the Dog/Male row, rather than the "total" of 12) ?

Animal Gender Born Sold Grand Total
Dog Male 10 2 12
Female 5 2 7
Cat Male 1 1 2
Female 3 2 5
Hamster Male 6 3 9
Female 2 1 3



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html