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.v23.2.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.
Difference From (PivotShowValuesAsType.Difference)
Displays values as the difference from the value of the base item in the base field.
% Of (PivotShowValuesAsType.Percent)
Displays values as the percentage of the value of the base item in the base field.
% Difference From (PivotShowValuesAsType.PercentDifference)
Displays values as the percentage difference from the value of the base item in the base field.
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.
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)
Related GitHub Examples
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.