Skip to main content

DevExpress v24.2 Update — Your Feedback Matters

Our What's New in v24.2 webpage includes product-specific surveys. Your response to our survey questions will help us measure product satisfaction for features released in this major update and help us refine our plans for our next major release.

Take the survey Not interested

How to: Change the Summary Function for a Data Field

  • 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

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 (the workbook is opened in Microsoft® Excel®).

PivotTable_ValueFieldSettings_SummarizeValuesBy_Average