## Data Field Calculation

Normally, data fields show a value based on the Function property of the field. On the other hand, Excel also permits us to change the meaning of the value that is displayed in a data field.

### 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.

Baseltem 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/Baseltem

The Calculation values that do not require a BaseField/Baseltem 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

Each cell is replaced by that cell's percentage of the sum of the values in the entire table (for the given data field).

The formula to compute the value in a cell is:

new value = (current value * grand total) / (row total * column total)

Figures 20-28 through 20-31 illustrate these calculations, starting with the pivot table in Figure 20-10. Note that the percent calculations require that the grand row and column totals be displayed. The code for Figure 20-28 is:

ActiveSheet.PivotTables("Sales&Trans").

PivotFields("Sale").Calculation = xlRunningTotal

Referring to Figure 20-28, cell D6 contains sales for Boston, cell D10 contains total sales for Boston and Los Angeles, and cell D14 contains total sales for Boston, Los Angeles, and New York. (I have had reports that the Calculation property does not always work properly. For some reason, it may simply cause the relevant cells to fill with Excel's infamous #N/A symbols. Indeed, I have had this same experience at times, but I have not been able to figure out why.)

Figure 20-28. Calculation = xlRunningTotal

 3 Period 1 4 Store Store Type Data 1 2 3 4 5 BO SI Company Trans 28248 2 ¿714 23672 23602 6 Sato 44673 46256 46223 7 Franchise Trans 13993 13942 13275 132 10 S Sale 21816 21739 13632 1S325 9 LOS Company Trans 34538 35938 35692 35001 10 Sale 168156 17S491 172596 171641 11 Franchise Trans 7 1533 72947 75619 74392 12 Sale 2342<17 290013 297957 292356 13 NEW Company Trans 24616 25104 27015 26354 14 Sate 24624? 251742 'A; -U' 259699 15 F ranch ise Trans 53273 54351 51 £22 51218 16 Sale 460600 4724-74 463494 460302

The code for Figure 20-29 is:

ActiveSheet.PivotTables("Sales&Trans").

PivotFields("Sale").Calculation = xlPercentOfRow

Figure 20-29. Calculation = xlPercentOfRow

 A B C D E F G H 1 Year (A: ^ 2 Period 4 ttone C ity SL.ore Type Data 1 2 3 4 Grand Total 5 eOSTt )N rran&hise Tr-^nc- 13903 13943 13275 13210 51616 36104 27015 ?68>1 10-1589 14 Sale 2? 0J % 25.13% 25 9 1% 2'i: 95% 100 00% 15 Franchise Trans 53273 54351 51822 51218 210004 16 Sale 2531% 26 19% 24 4"% 24 03% 100 00% .1 -r

The code for Figure 20-30 is:

ActiveSheet.PivotTables("Sales&Trans").

PivotFields("Sale").Calculation = xlPercentOfColumn

Figure 20-30. Calculation = xlPercentOfColumn

 A B C D E F G H I Year (AN)__.7. 2 3 Period 4 Store Stone Type Data 1 2 4 Grand Total 5 BOS" Company Trans 23248 28714 28872 23502 114236 6 Sale i? 32% 6 39% 6 35% 3 42% 6 37% 7 Franchise Trans 13993 13942 13275 13210 54420 8 Sals 3 09% 2.96% 2 56% 2 55% 2 79% 9 I OS rompany Tran--, 345,33 3593ft 3569" 35TJÜ1 141219 to Sa^e 17 47% 17.85% 17 33% 17 42% 17.47% 11 Franchise Trans 7 l\$S3 72947 IS 74^92 29454 I 12 Sale 37 13% 38 32% ■■ | 37 53% 13 NEW Company Tians 24616 281Ü4 27015 2S&54 104589 14 Sals 11 05% 116 1% 1 ? ,":6% 1/ ;3% 11 74% 15 Fra-xhisi Trans 53273 5435 1 51822 51213 210804 16 24 95% 24.85% 23 39% 2 S 26% 24 11% 17 Torsi T-ans 226301 231996 232095 229277 919869 16 Total Sale '00.00% 1 00.00% 100 00% 103C0% 10000% in

The code for Figure 20-31 is:

ActiveSheet.PivotTables("Sales&Trans").

PivotFields("Sale").Calculation = xlPercentOfTotal

Figure 20-31. Calculation = xlPercentOfTotal

 Period Store Store Type Data 12 3 4 Grand Total BOS1 Company Trans Sale 2324\$ 28714 28672 28602 1 55% 1 62% 1 60% 1 60% 114236 6 37% Franchise Trans Sale 13993 13942 13275 13210 0 75% 0.75% 0.64% 0.63% 54420 2.79% LOS Company Trans 34538 35933 35692 35001 4 27% 4.48% 4 37% 4.34% 14:219 17.47% Franchise Trans Sale 71583 72947 75619 74392 9 03% 9.28% 0.67% 9.50% ¿94541 37.53% NEW Company Trans Sgte 24616 26104 27015 26 £¡54 ? 70% ? QS% 3 04% 3 05% S04539 11 74% Franchise Trans Sale 53273 54351 5I622 51213 6.10% 0.31% 5.90% 5.79% 2 10664 24.1 f% Tctal i nans 226301 23190S 232095 229277 919669 Total Sale 24 40% 25.40% 25.22% 24.91% 10000%

20.7.8.3 Calculations requiring a BaseField/BaseItem

The procedure for making calculations with a BaseField/BaseItem is not explained very well in the documentation, so let us see if we can clear it up by first considering an example. Consider the code:

With ActiveSheet.PivotTables("Sales&Trans"). PivotFields("Sale") .Calculation = xlDifferenceFrom .BaseField = "Store City" .BaseItem = "Boston" End With

Referring to Figure 20-32, we have reproduced our usual pivot table (from Figure 20-10) with several changes. First, we removed the Trans values, since they are not relevant to our example. We have also replaced the Sale values for the given BaseField and BaseItem by symbols (b1-b8). Finally, we replaced the other Sale values with number signs, since we do not care about the actual values.

Figure 20-32. Illustrating a calculation

A

B

C

D

E

F

G

1

Period

2

Store City

Store Typi

Data

1

2

3 4

3

BCSTON

Company

'I rani

4

Stile

bl

b2

b3

b4

5

Franchise

Trans

Sale

b5

bG

b7

be

7

LOS ANGI

Company

Trans

3

Sale

#

k

k

I*

9

Franc hise

Trans

10

Sale

#

k

k

11

NEW YOR

Company

Trans

'2

Sate

#

k

'3

Franchise

Trans

14

Sale

#

k

k

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

 A B C D E F G 1 Period Store City Store Typ( Data 1 2 3 4 BOSTON Company Trans 4 Sale 0 0 0 0 5 FraiTçh se Tr\$ii\$ Säle 0 0 0 0 7 LOS ANGI Company Trans 0 Sala s-bi if-b? S rranch se Trans 10 Sale £b5 *-b6 ^■b7 itf-b8 11 NEW VOR Corrçgny Trans 12 S sie £-b4 13 branch se Trans 14 Sale #-b5 s^be #-h7

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" .Baseltem = "Boston" End With Figure 20-10.

Figure 20-34. Illustrating the Calculation property

 A B C D E F G 1 Year (AH) h[ 2 3 Period | Slore Store Type Data 12 3 4 6 7 BO SI Company Trans Sate 28248 28714 23672 23602 Franchise Trans Sale 13993 13942 13275 13210 10 11 12 LOS Company Trans Sale 34533 35938 35692 35001 78800 32637 30084 79195 Franchise Trans Sale 71583 72947 75619 74302 240615 2<1 £535 260693 256206 16 NEW Company Trans Sale 24616 25104 27015 26354 33411 38324 4164^ 41835 Franchi se Trans Sale 53273 54351 51822 51218 154537 160722 151905 149121
0 0