Running Total

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

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

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

Figure 12.44

This pivot table presents four views of Sum of Revenue.Column K is the normal calculation. Column L is % of Total. Column M is % change from previous month. Column N is the running total.

Data

Data

Figure 12.44

This pivot table presents four views of Sum of Revenue.Column K is the normal calculation. Column L is % of Total. Column M is % change from previous month. Column N is the running total.

ShipDale 1ยป

Tola Revenue

PclOfTotal

%Chanqe

YTD Total

Jan

0 0

Post a comment