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
Bookmarks