Microsoft Excel —

Percentage Growth from Previous Month

With ship months going down the columns, you might want to see the percentage of revenue growth from month to month. You can set this up with the xlPercentDifferenceFrom setting. In this case, you must specify that the BaseField is "In Balance Date" and that the BaseItem is something called (previous):

' Set up % change from prior month
With PT.PivotFields("Revenue")
    .Orientation = xlDataField
    .Function = xlSum
    .Caption = "%Change"
    .Calculation = xlPercentDifferenceFrom
    .BaseField = "In Balance Date"
    .BaseItem = "(previous)"
    .Position = 3
    .NumberFormat = "#0.0%"
End With

Note that with positional calculations, you cannot use the AutoShow or AutoSort method. This is too bad; it would be interesting to sort the customers high to low and to see their sizes in relation to each other.


Percentage of a Specific Item

Many companies have a goal to have service revenue exceed a certain multiplier of copier sales. You can use the xlPercentDifferenceFrom setting to express revenues as a percentage of the copier product line:

' Show revenue as a percentage of hardware
With PT.PivotFields("Revenue")
    .Orientation = xlDataField
    .Function = xlSum
    .Caption = "% of Copier"
    .Calculation = xlPercentDifferenceFrom
    .BaseField = "ProductLine"
    .BaseItem = "Copier Sale"
    .Position = 3
    .NumberFormat = "#0.0%"
End With