Hello -

Looking for help calculating inside the pivot table -

I have a table that have different days of outage dates and I need to calculate the average of the "Capacity offline" by the day, week (Monday-Sunday) and month.

In the xls, I have outage date, owner name, plant name, plantID, Capacity offline, Type and Unit type...

PlantID will be use as the Key source since it is the same ID for all plant name and owner name...


I was able to create a pivot table but wasn't able to figure out how the calculation work...

codes for pivot table below,, please help

Option Explicit

Sub CreatePivotTable()
    Dim PTcache As PivotCache
    Dim PT As PivotTable
    
    On Error Resume Next
    Sheets("Sheet1").DrawingObjects("TextBoxWait").Visible = True
    On Error GoTo 0
    Application.ScreenUpdating = False

'   Delete PivotSheet if it exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("PivotSheet").Delete
    On Error GoTo 0
    
'   Create a Pivot Cache
    Set PTcache = ActiveWorkbook.PivotCaches.Add( _
      SourceType:=xlDatabase, _
      SourceData:=Range("A1").CurrentRegion.Address)

'   Add new worksheet
    Worksheets.Add
    ActiveSheet.Name = "PADD"

'   Create the Pivot Table from the Cache
    Set PT = PTcache.CreatePivotTable( _
      TableDestination:=Sheets("PADD").Range("A1"), _
      TableName:="PADD1Pivot")
    
    With PT
'       Add fields
    .PivotFields("UNIT TYPE").Orientation = xlDataField
        .PivotFields("OUTAGE DATE").Orientation = xlRowField
        .PivotFields("OWNER NAME").Orientation = xlRowField
        .PivotFields("PLANT NAME").Orientation = xlRowField
        .PivotFields("PLANTID").Orientation = xlRowField
        .PivotFields("TYPE").Orientation = xlColumnField
        
        .PivotFields("CAPACITY OFFLINE").Orientation = xlRowField
        '.PivotFields("").Orientation = xlDataField
    
'       Add a calculated field to compute weekly data divide it by 7 Monday -Sunday
        .CalculatedFields.Divide "week", "=Capacity offline*7"
        .PivotFields("week").Orientation = xlDataField
       
'

    End With
    Application.ScreenUpdating = True
    On Error Resume Next
    Sheets("Sheet1").DrawingObjects("TextBoxWait").Visible = False
End Sub