Microsoft Office Tutorials and References

In Depth Information

**20.7.8.1 Calculation property**

20.7.8.1 Calculation property

This is done by setting the Calculation property for the data field. (The property applies only to

data fields.) The possible values of this property are given by the following enum:

Enum XlPivotFieldCalculation

xlNoAdditionalCalculation = -4143

xlDifferenceFrom = 2

xlPercentOf = 3

xlPercentDifferenceFrom = 4

xlRunningTotal = 5

xlPercentOfRow = 6

xlPercentOfColumn = 7

xlPercentOfTotal = 8

xlIndex = 9

End Enum

As you can see from these constants, the idea is to replace the raw value in the data field by a type

of relative value. (We will see an example in a moment.)

Note that for some values of Calculation, additional information is required. In particular, if

Calculation is equal to
xlDifferenceFrom
,
xlPercentDifferenceFrom
, or

xlPercentOf
, then we must specify the following two properties:

BaseField property

This property, which applies only to data fields, returns or sets the field upon which the

data field calculation is based.

BaseItem property

This property, which applies only to data fields, returns or sets the item in the base data

field used for the calculation.

20.7.8.2 Calculations not requiring a BaseField/BaseItem

The Calculation values that do not require a BaseField/BaseItem pair are:

xlRunningTotal

Keeps a running total of all values, going down the rows of the table.

xlPercentOfRow

Each cell is replaced by that cell's percentage of the sum of the values in that cell's row

(for the given data field).

xlPercentOfColumn

Each cell is replaced by that cell's percentage of the sum of the values in that cell's

column (for the given data field).

xlPercentOfTotal