Microsoft Excel —

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)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
        xlDatabase, SourceData:=PRange.Address)

    ' 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
    PT.AddFields RowFields:="Line of Business"

    ' Define calculated item along the product dimension
    PT.PivotFields("Line of Business").CalculatedItems _
        .Add "PrinterCopier", "='Copier Sale'+'Printer Sale'"
    ' Resequence so that the report has printers and copiers first
    PT.PivotFields("Line of Business"). _
        PivotItems("Copier Sale").Position = 1
    PT.PivotFields("Line of Business"). _
        PivotItems("Printer Sale").Position = 2
    PT.PivotFields("Line of Business"). _
        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

15. After the components that make up the calculated PrinterCopier item are hidden, the total revenue for the company is again correct. However, it would be easier to add a new field to the original data with a Responsibility field.