officefileapi-118511-spreadsheet-document-api-examples-pivot-tables-how-to-create-a-calculated-item.md
If the predefined aggregation functions or Show Values As calculation options do not meet your requirements, you can create your own formulas to calculate values in a PivotTable report by inserting calculated fields and calculated items. A calculated item is a custom item in a PivotTable field whose value is produced based on values of other items in the same field.
All calculated items added to a PivotTable field are stored in the PivotCalculatedItemCollection collection, which can be accessed using the PivotField.CalculatedItems property. Use the collection’s methods to create, modify or remove calculated items.
Before inserting a calculated item, take into account the following restrictions.
To create a calculated item, use the PivotCalculatedItemCollection.Add method. The first parameter of this method allows you specify a formula for the calculated item.
A formula string should conform to the common syntax rules and contain only supported elements.
In the formula, you can use constants and refer to other items in the same field where the calculated item resides. To create an item reference, use one of the approaches listed in the table below. All examples in the table refer to the pivot table shown later in this section.
You cannot create formulas that use a cell reference, defined name, circular references and arrays.
You cannot use worksheet functions that require cell references or defined names as arguments.
The formula cannot refer to the PivotTable’s subtotals, totals and Grand Total value.
The following code demonstrates how to create two calculated items to calculate total sales for each region.
Worksheet worksheet = workbook.Worksheets["Report10"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the pivot field by its name in the collection.
PivotField field = pivotTable.Fields["State"];
// Add calculated items to the "State" field.
field.CalculatedItems.Add("=Arizona+California+Colorado", "West Total");
field.CalculatedItems.Add("=Illinois+Kansas+Wisconsin", "Midwest Total");
Dim worksheet As Worksheet = workbook.Worksheets("Report10")
workbook.Worksheets.ActiveWorksheet = worksheet
' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Access the pivot field by its name in the collection.
Dim field As PivotField = pivotTable.Fields("State")
' Add calculated items to the "State" field.
field.CalculatedItems.Add("=Arizona+California+Colorado", "West Total")
field.CalculatedItems.Add("=Illinois+Kansas+Wisconsin", "Midwest Total")
The resulting PivotTable report is shown in the image below (the workbook is opened in Microsoft® Excel®).
To change a formula for a calculated item, get access to the required item by its index in the PivotCalculatedItemCollection collection and then assign a new formula to the item’s PivotItem.Formula property. To rename a calculated item, use the PivotItem.Caption property.
Worksheet worksheet = workbook.Worksheets["Report7"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the pivot field by its name in the collection.
PivotField field = pivotTable.Fields["Customer"];
// Add a calculated item to the "Customer" field.
PivotItem item = field.CalculatedItems.Add("='Big Foods'*110%", "Big Foods Sales Plan");
//Change the formula for the calculated item.
item.Formula = "='Big Foods'*115%";
Dim worksheet As Worksheet = workbook.Worksheets("Report7")
workbook.Worksheets.ActiveWorksheet = worksheet
' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Access the pivot field by its name in the collection.
Dim field As PivotField = pivotTable.Fields("Customer")
' Add a calculated item to the "Customer" field.
Dim item As PivotItem = field.CalculatedItems.Add("='Big Foods'*110%", "Big Foods Sales Plan")
'Change the formula for the calculated item.
item.Formula = "='Big Foods'*115%"
To remove a calculated item from the field, use the PivotCalculatedItemCollection.Remove or PivotCalculatedItemCollection.RemoveAt method. To remove all calculated items from the collection at once, use the PivotCalculatedItemCollection.Clear method.
Worksheet worksheet = workbook.Worksheets["Report7"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the pivot field by its name in the collection.
PivotField field = pivotTable.Fields["Customer"];
// Add a calculated item to the "Customer" field.
field.CalculatedItems.Add("='Big Foods'*110%", "Big Foods Sales Plan");
//Remove the calculated item by its index from the collection.
field.CalculatedItems.RemoveAt(0);
Dim worksheet As Worksheet = workbook.Worksheets("Report7")
workbook.Worksheets.ActiveWorksheet = worksheet
' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Access the pivot field by its name in the collection.
Dim field As PivotField = pivotTable.Fields("Customer")
' Add a calculated item to the "Customer" field.
field.CalculatedItems.Add("='Big Foods'*110%", "Big Foods Sales Plan")
'Remove the calculated item by its index from the collection.
field.CalculatedItems.RemoveAt(0)