Skip to main content
Row

PivotDataField.ShowValuesWithCalculation(PivotShowValuesAsType, PivotField, PivotItem) Method

Applies the “Show values as” calculation option to the data field to change the way summary values are displayed within the report.

Namespace: DevExpress.Spreadsheet

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

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

void ShowValuesWithCalculation(
    PivotShowValuesAsType calculationType,
    PivotField baseField,
    PivotItem baseItem
)

Parameters

Name Type Description
calculationType PivotShowValuesAsType

A PivotShowValuesAsType enumeration member specifying how summary values should be displayed within the data field.

baseField PivotField

A PivotField object that specifies a base field for a custom calculation.

baseItem PivotItem

A PivotItem object specifying a base item in the base field that should be used for calculation.

Remarks

When working with pivot tables, it is often more important to understand how a summary value correlates with other values in the data area than to know the summary value itself. The current ShowValuesWithCalculation method overload allows you to compare item values for a specific field (base field) and return the absolute or percentage difference between them, or calculate the percentage of each item value in the base field with respect to a selected base item.

Important

Only a field from the row or column area can be used as a base field, otherwise, all results will be shown as a #N/A error.

In particular, using this method, you can apply the following types of custom calculations.

Note that this method allows you to use a specific field item as the basis for a custom calculation. To use the previous or next item in the base field as a base item for calculation, call the ShowValuesWithCalculation method overload with the PivotBaseItemType.Previous or PivotBaseItemType.Next enumeration value passed as the third parameter.

The following example demonstrates how to calculate the percentage of each quarter’s sales with respect to the first quarter. Data in the report has been summarized using the “Sum” function.

View Example

Dim worksheet As Worksheet = workbook.Worksheets("Report14")
workbook.Worksheets.ActiveWorksheet = worksheet

' Access the pivot table by its name in the collection
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")

' Access the data field by its index in the collection.
Dim dataField As PivotDataField = pivotTable.DataFields(0)
' Select the base field ("Quarter"). 
Dim baseField As PivotField = pivotTable.Fields("Quarter")
' Select the base item ("Q1"). 
Dim baseItem As PivotItem = baseField.Items(0)
' Show values as the percentage of the value of the base item in the base field. 
dataField.ShowValuesWithCalculation(PivotShowValuesAsType.Percent, baseField, baseItem)

PivotField_ValueFieldSettings_ShowValuesAs_PercentOf

The following code snippets (auto-collected from DevExpress Examples) contain references to the ShowValuesWithCalculation(PivotShowValuesAsType, PivotField, PivotItem) method.

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