PivotDataField.ShowValuesWithCalculation(PivotShowValuesAsType, PivotField, PivotBaseItemType) 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,
PivotBaseItemType baseItemType
)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. |
| baseItemType | PivotBaseItemType | A PivotBaseItemType enumeration member specifying whether the previous or next item in the base field should be used as the basis 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 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 only the previous or next item in the base field as a base item for calculation. To use a specific item for a custom calculation, call the ShowValuesWithCalculation method overload with the baseItem parameter.
The following example demonstrates how to calculate the difference in product sales between the current quarter and the previous quarter. Data in the report has been summarized using the “Sum” function.
Worksheet worksheet = workbook.Worksheets["Report14"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the data field by its index in the collection.
PivotDataField dataField = pivotTable.DataFields[0];
// Display the difference in product sales between the current quarter and the previous quarter.
dataField.ShowValuesWithCalculation(PivotShowValuesAsType.Difference, pivotTable.Fields["Quarter"], PivotBaseItemType.Previous);
Related GitHub Examples
The following code snippets (auto-collected from DevExpress Examples) contain references to the ShowValuesWithCalculation(PivotShowValuesAsType, PivotField, PivotBaseItemType) 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.