The requested page is not available for the requested platform. You are viewing the content for .NET Framework 4.5.2+ platform.
.NET Framework 4.5.2+
.NET Framework 4.5.2+
.NET Standard 2.0+
.NET Core 3.0+
Row

PivotDataField.SummarizeValuesBy Property

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

Namespace: DevExpress.Spreadsheet

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

Declaration

PivotDataConsolidationFunction SummarizeValuesBy { get; set; }
Property SummarizeValuesBy As PivotDataConsolidationFunction
PivotDataConsolidationFunction SummarizeValuesBy { get; set; }
Property SummarizeValuesBy As PivotDataConsolidationFunction
PivotDataConsolidationFunction SummarizeValuesBy { get; set; }
Property SummarizeValuesBy As PivotDataConsolidationFunction

Property Value

Type Description
PivotDataConsolidationFunction

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

Property Value

Type Description
PivotDataConsolidationFunction

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

Property Value

Type Description
PivotDataConsolidationFunction

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

Remarks

By default, when you add a numeric field to the data area, its values are summarized by the Sum function. For a text field or field containing blank cells, the Count function is used as the default summary function. However, you can select a different function to summarize values in the data field by setting the SummarizeValuesBy property to the required PivotDataConsolidationFunction enumeration value.

For example, the following code creates a pivot table that uses the "Average" function in the data field to show the average of sales values for each product.

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]* "" - ""??_);_(@_)"

Note that the selected summary function will be automatically used for the subtotals and grand totals in the report. To select a different function for the totals of a specific pivot field, use the PivotField.SetSubtotal method.

You can also change the way summaries are displayed in a pivot table by applying an additional calculation option to the raw summary values. To do this, use the PivotDataField.ShowValuesWithCalculation method overloads.

See Also