Microsoft Office Tutorials and References

In Depth Information

**Figure 20-33. The finished calculation**

Now, the trick in seeing how the calculations are made is to fix a value for the fields other than the

base fieldâ€”in our case the Store Type and Period. Consider, for instance, the values:

Store Type = "Company"

Period = 1

The Sale data cells corresponding to these values are grayed in
Figure 20-32
. One of these cells

(cell D4) is the base item cell. For this Store Type/Period combination, a calculation is made using

the value in this cell as the base value. In our case, it is the
xlDifferenceFrom
calculation that

is being made. Hence, the base value is subtracted from the values in all three grayed cells. This

gives the table in
Figure 20-33
.
Note that the base value is even subtracted from itself, giving 0.

This is done for each Store Type/Period combination, as shown in
Figure 20-33
.

Figure 20-33. The finished calculation

The formulas for the Calculation property that require BaseField/BaseItem values are:

xlDifferenceFrom

# - base value

xlPercentOf

#/base value
(expressed as a percent)

xlPercentDifferenceFrom

(# - base value)/base value
(expressed as a percent)

To illustrate,
Figure 20-34
shows the actual effect of the earlier code on
Figure 20-10
:

With ActiveSheet.PivotTables("Sales&Trans"). _

PivotFields("Sale")

.Calculation = xlDifferenceFrom

.BaseField = "Store City"

.BaseItem = "Boston"

End With
Figure 20-10
.

Figure 20-34. Illustrating the Calculation property