Microsoft Excel —

Running Total

It is not intuitive, but to set up a running total, you must define a BaseField. In this example, you have In Balance Date running down the column. To define a running total column for revenue, you must specify that BaseField is "In Balance Date":

' Set up Running Total
With PT.PivotFields("Revenue")
    .Orientation = xlDataField
    .Function = xlSum
    .Caption = "YTD Total"
    .Calculation = xlRunningTotal
    .Position = 4
    .NumberFormat = "#,##0,K"
    .BaseField = "In Balance Date"
End With

Figure 21 shows the results of a pivot table with three custom calculation settings, as discussed earlier.

21. This pivot table presents four views of Sum of Revenue. Column O is the normal calculation. Column P is % of Total. Column Q is % change from previous month. Column R is the running total.