# Calculated Items

Say that in your company the vice president of sales is responsible for copier sales and printer sales. The idea behind a calculated item is that you can define a new item along the Line of Business field to calculate the total of copier sales and printer sales. Listing 3 produces the report shown in Figure 14.

##### Listing 3. Code That Adds a New Item Along the Line of Business Dimension
```Sub CalcItemsProblem()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long

Set WSD = Worksheets("PivotTable")
Dim WSR As Worksheet

' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT

' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)

' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")

' Turn off updating while building the table
PT.ManualUpdate = True

' Set up the row fields

' Define calculated item along the product dimension
' Resequence so that the report has printers and copiers first
PivotItems("Copier Sale").Position = 1
PivotItems("Printer Sale").Position = 2
PivotItems("PrinterCopier").Position = 3

' Set up the data fields
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0"
End With

' Ensure that you get zeroes instead of blanks in the data area
PT.NullString = "0"

' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True

End Sub
```

##### 14. Unless you love restating numbers to the SEC, avoid using calculated items.

Look closely at the results shown in Figure 14. The calculation for PrinterCopier is correct. PrinterCopier is a total of Printers + Copiers. Some quick math confirms that 86 million + 68 million is about 154 million. However, the grand total should be 154 million + 83 million + 574 million, or about 811 million. Instead, Excel gives you a grand total of \$968 million. The total revenue for the company just increased by \$150 million. Excel gives the wrong grand total when a field contains both regular and calculated items. The only plausible method for dealing with this is to attempt to hide the products that make up PrinterCopier. The results are shown in Figure 15:

```With PT.PivotFields("Line of Business")
.PivotItems("Copier Sale").Visible = False
.PivotItems("Printer Sale").Visible = False
End With
```