officefileapi-devexpress-dot-spreadsheet-dot-pivotdatafield-61970a98.md
Gets or sets the summary function used to calculate values in the data field.
Namespace : DevExpress.Spreadsheet
Assembly : DevExpress.Spreadsheet.v25.2.Core.dll
NuGet Package : DevExpress.Spreadsheet.Core
PivotDataConsolidationFunction SummarizeValuesBy { get; set; }
Property SummarizeValuesBy As PivotDataConsolidationFunction
| 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.
| | 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.
| | StdDevp |
The standard deviation of a population, where the population is all of the data to be summarized.
| | 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.
| | Varp |
The variance of a population, where the population is all of the data to be summarized.
|
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]* "" - ""??_);_(@_)";
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]* "" - ""??_);_(@_)"
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.
// Use the "Average" function to summarize values in the data field.
dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average;
// Specify the number format for the data field.
// Use the "Average" function to summarize values in the data field.
dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average;
// Specify the number format for the data field.
// Use the "Average" function to summarize values in the data field.
dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average;
// Specify the number format for the data field.
' Use the "Average" function to summarize values in the data field.
dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average
' Specify the number format for the data field.
' Use the "Average" function to summarize values in the data field.
dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average
' Specify the number format for the data field.
' Use the "Average" function to summarize values in the data field.
dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average
' Specify the number format for the data field.
See Also