Skip to main content
A newer version of this page is available. .
.NET Framework 4.5.2+
Row

PivotDataField.SummarizeValuesBy Property

Gets or sets the summary function used to calculate values in the data field.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v21.2.Core.dll

Declaration

PivotDataConsolidationFunction SummarizeValuesBy { get; set; }

Property Value

Type Description
PivotDataConsolidationFunction

An enumeration value that specifies the summary function used by the data field.

Remarks

When you add a numeric field to the data area, the field values are summarized by the Sum function. For a text field or field with blank cells, the default summary function is Count. Use the SummarizeValuesBy property to select a different function to summarize values in the data field.

Note

When you change a summary function for a data field, the Spreadsheet rebuilds the pivot table. As a result, a custom name specified for this data field is reset.

The following code example creates a pivot table that uses the Average function to show average sales values for each product:

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 selected summary function is automatically used for subtotals and grand totals in the report. To select a different function for the totals of a specific pivot field, call the PivotField.SetSubtotal method.

You can also use the PivotDataField.ShowValuesWithCalculation method overloads to change how summaries are displayed in a pivot table. These methods allow you to apply an additional calculation option to the raw summary values.

The following code snippets (auto-collected from DevExpress Examples) contain references to the SummarizeValuesBy property.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

See Also