## Calculated Items and Calculated Fields

We have seen that it is possible to add a calculated field to a pivot table. A calculated field is special type of PivotField object that is not part of the original source data, but, instead, is calculated from source fields using a formula. Note that there is no such thing as a CalculatedField object, but there is a CalculatedFields collection.

As we have seen, to create a new calculated field, we use the Add method of the CalculatedFields collection of the PivotTable object. The syntax is:

where Name is the name of the field and Formula is the formula for the field.

On the other hand, a calculated item is a special type of Pivotltem object associated with a given PivotField object. (There is no such thing as a Calculatedltem object, but there is a Calculatedltems collection.) The values of this item are calculated by using a formula.

The PivotField object has a Calculatedltems collection of all calculated items for that pivot field. To create a new calculated item, we use the Add method of the Calculatedltems object. This method has the same syntax as the Add method of the CalculatedFields object:

where Name is the name of the field and Formula is the formula for the field.

To illustrate, the following code adds a new calculated item to the Store Type pivot field:

ActiveSheet.PivotTables("Sales&Trans").

PivotFields("Store Type").CalculatedItems.

The results are shown in Figure 20-42, where the calculated item is CompanyX2. The value in each of the CompanyX2 cells is twice the value in the corresponding Company cell.

Figure 20-42. Illustrating a calculated item (CompanyX2)

 A E C D E F G 1 Year (All) z Period 4 Store City Store Type Data l 2 3 4 5 BOSTON Company Trans 2-3246 28714 28672 28602 6 Sale 44678 46927 46256 46223 1 Franchise Trans 13993 13942 13275 13210 8 Sale 21816 21739 18632 18325 9 Company .X 2 Tran» 57423 57344 57204 10 Sale '33356 93854 92512 92446 11 LOS ANGELES Company Trans -,4588 35938 35692 35001 12 Sals 123473 1295*1 126340 12541 & 13 Franchise Trans 71583 72947 756-19 74392 14 Sale ,-:>.I 268274 27ii.v2? 274531 15 Company\2 Trans 69176 71876 7 1364 70 002 10 Sale 246956 259128 252680 250836 17 NEW YORK Company Trans 24616 20104 V 20854 13 Sale 78089 35251 87905 88053 19 Franchise Trans 5436 I 5132; 51218 2C Sale ' 162461 17053? ' L ■'! 1 21 CompanyX? Trans 49222 52208 54030 53703 22 Sale -SC17S - -i <;o2 175810 170116

For comparison, let us add a calculated field to the pivot table in Figure 20-42. We will add the same calculated field that we added when we discussed the CalculatedFields method earlier in the chapter:

.Orientation = xlDataField .Name = "Avg Check" .NumberFormat = "##.#" End With

The result is shown in Figure 20-43.

Figure 20-43. Illustrating a calculated field and calculated item m

10 11 12

16 17 13 13 20 21 22

25 20 27 23

Year

 Period Store- Ci'-y Stone Typo Data 1 2 3 4 BOSTON Company 23248 28714 28672 236D2 Sate 44673 46927 46256 46223 Avg Check 1 6 1 6 1.6 1 6 Franchise Trans 13993 13942 132.'t> 13210 Sale 21316 21739 18632 13325 Avg Check 1.6 1 6 1.4 1 4 Comp any X2 Trans 56496 57428 57344 57204 Sate 89356 93854 92'j 12 92446 Au-:; Check 1 6 1 6 1.6 1 6 LOS ANGELES Company Trans 34583 35938 35692 35001 Sale 123-178 129554 1263-10 125-118 Avg Chec;-. 3.6 36 3.5 3.6 Franchise Trans 71583 723-17 7561G 74332 Sate 262431 268274 279325 274531 Avg Check 3 7 5 7 3 7 3 7 Comp any X2 Titinç 69176 71876 71384 70002 Sate /4S956 259123 2576R0 75GS35 Avg Check 3 6 36 3 J. 3 6 MEW TORK Comp an v Trans 24*16 27015 26Ô54 Sate 730*9 8525 I 87905 83058 Avg Chi-tk 32 3 3 33 3 3 Franchise Trans 53273 -34;.-: I 51322 51213 Sate 170353 182401 170537 167440 Avg Chech 3.3 3 1 3.3 3.3 Comp any X2 Trans 49232 5220S ^4ù 30 53!'08 Sale 156173 1705Û2 175310 176116 Avg Cheek 32 3 3 3.3 3 3

Note that the ListFormulas method of the PivotTable object will produce a list (on a separate worksheet) of all formulas in the pivot table. The outcome for the pivot table in Figure 20-43 is shown in Figure 20-44.

Figure 20-44. The output of ListFormulas

Figure 20-44. The output of ListFormulas

Let us conclude by recalling that the read-only IsCalculated property can be used to determine whether or not a pivot field or pivot item is calculated.

+1 0

### Responses

• gerardino
How to add a calculated field in a pivot table using vba?
6 months ago