Skip to main content
Row

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.v24.1.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.

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.

View Example

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);

PivotField_ValueFieldSettings_ShowValuesAs_DifferenceFrom

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.

See Also