How to: Change the Summary Function for a Data Field

• Jun 07, 2019
• 2 minutes to read

To change the default summary function applied to a data field in the PivotTable report, assign the required PivotDataConsolidationFunction enumeration value to the PivotDataField.SummarizeValuesBy property.

For example, the following code demonstrates how to summarize values in a data field using the “Average” built-in function.

View Example

ValueFieldSettingsActions.vb

Dim sourceWorksheet As Worksheet = workbook.Worksheets("Data5")
Dim worksheet As Worksheet = workbook.Worksheets.Add()
workbook.Worksheets.ActiveWorksheet = worksheet

' Create a pivot table using the cell range "A1:E65" as the data source.
Dim pivotTable As PivotTable = worksheet.PivotTables.Add(sourceWorksheet("A1:E65"), worksheet("B2"))

' Add the "Category" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields("Category"))
' Add the "Product" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields("Product"))

' Add the "Amount" field to the data area.
Dim dataField As PivotDataField = pivotTable.DataFields.Add(pivotTable.Fields("Amount"))
' Use the "Average" function to summarize values in the data field.
dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average
' Specify the number format for the data field.
dataField.NumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"

ValueFieldSettingsActions.cs

Worksheet sourceWorksheet = workbook.Worksheets["Data5"];
Worksheet worksheet = workbook.Worksheets.Add();
workbook.Worksheets.ActiveWorksheet = worksheet;

// Create a pivot table using the cell range "A1:E65" as the data source.
PivotTable pivotTable = worksheet.PivotTables.Add(sourceWorksheet["A1:E65"], worksheet["B2"]);

// Add the "Category" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields["Category"]);
// Add the "Product" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields["Product"]);

// Add the "Amount" field to the data area.
PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Amount"]);
// Use the "Average" function to summarize values in the data field.
dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average;
// Specify the number format for the data field.
dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)";

The image below shows the resulting PivotTable report.