Skip to main content
Row

PivotDataField.SummarizeValuesBy Property

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

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v24.1.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

PivotDataConsolidationFunction SummarizeValuesBy { get; set; }

Property Value

Type Description
PivotDataConsolidationFunction

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

Available values:

Show 11 items
Name Description
Average

The average of the values.

func_average

Count

The number of nonempty values.

CountNumbers

The number of numeric values.

Max

The largest value.

Min

The smallest value.

Product

The product of the values.

StdDev

An estimate of the standard deviation of a population, where the sample is a subset of the entire population.

func_stddev

StdDevp

The standard deviation of a population, where the population is all of the data to be summarized.

func_stddevp

Sum

The sum of the values.

Var

An estimate of the variance of a population, where the sample is a subset of the entire population.

func_var

Varp

The variance of a population, where the population is all of the data to be summarized.

func_varp

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:

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 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