[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Collapse]WPF Controls
 [Expand]What's Installed
 [Expand].NET Core 3 Support
 [Expand]Common Concepts
 [Expand]MVVM Framework
 [Collapse]Controls and Libraries
  [Expand]Data Grid
  [Expand]Ribbon, Bars and Menu
  [Expand]Charts Suite
  [Expand]Pivot Grid
    Product Structure
   [Expand]Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
   [Expand]Pivot Table Overview
   [Expand]Charts and Graphics
    Operation Restrictions
    Find and Replace
   [Expand]Mail Merge
   [Expand]Visual Elements
    Themes and Templates
    [Expand]Rows and Columns
    [Expand]Data Binding
    [Expand]Formatting Cells
    [Expand]Conditional Formatting
    [Expand]Group Data
    [Expand]Filter Data
    [Collapse]Pivot Tables
      How to: Create a Pivot Table
      How to: Refresh a Pivot Table
      How to: Change a Data Source for a Pivot Table
      How to: Move a Pivot Table
      How to: Clear or Remove a Pivot Table
      How to: Change the PivotTable Layout
      How to: Subtotal Fields in a Pivot Table
      How to: Display or Hide Grand Totals for a Pivot Table
      How to: Format Numbers and Dates in a Pivot Table
      How to: Apply a Predefined Style to a Pivot Table
      How to: Apply a Custom Style to a Pivot Table
      How to: Control Style Options
      How to: Change the Summary Function for a Data Field
      How to: Apply a Custom Calculation to a Data Field
      How to: Create a Calculated Field
      How to: Create a Calculated Item
      How to: Sort Items in a Pivot Table
      How to: Filter Items in a Pivot Table
      How to: Group Items in a Pivot Table
    [Expand]Mail Merge
    [Expand]Cell Templates
  [Expand]Rich Text Editor
  [Expand]Tree List
  [Expand]Gauge Controls
  [Expand]Map Control
  [Expand]Layout Management
  [Expand]Windows Modern UI
  [Expand]Data Editors
  [Expand]Navigation Controls
  [Expand]Spell Checker
  [Expand]Property Grid
  [Expand]PDF Viewer
  [Expand]TreeMap Control
  [Expand]Gantt Control
  [Expand]Diagram Control
  [Expand]Windows and Utility Controls
   Dialogs, Notifications and Panels
  [Expand]Scheduler (legacy)
 [Expand]Scaffolding Wizard
  Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Report and Dashboard Server
[Expand]eXpressApp Framework
[Expand]eXpress Persistent Objects
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

How to: Create a Calculated Field

If the predefined aggregation functions or Show Values As calculation options do not meet your requirements, you can create your own formulas to calculate values in a PivotTable report by inserting calculated fields and calculated items.

All calculated fields for a pivot table are stored in the PivotCalculatedFieldCollection collection, which can be accessed using the PivotTable.CalculatedFields property. Use the collection's methods to create, modify or remove a calculated field.

Expanded Create a Calculated Field

To create a calculated field, use the PivotCalculatedFieldCollection.Add method. The first parameter of this method allows you specify a formula for the calculated field.

A formula string should conform to the common syntax rules and contain only supported elements.

  • In the formula, you can use constants and refer to other fields in the PivotTable report. The calculation will be performed on the sum of the underlying data for any fields in the formula. When you reference a field in your formula, you can enclose its name in apostrophes or omit them.
  • You cannot create formulas that use a cell reference, defined name, circular references and arrays.
  • You cannot use worksheet functions that require cell references or defined names as arguments.
  • The formula cannot refer to the PivotTable's subtotals, totals and Grand Total value.

After the calculated field is created, add it to the PivotTable's data area using the PivotDataFieldCollection.Add method of the PivotTable.DataFields collection.


Calculated fields are stored in the PivotCache and available to all pivot tables that share the same cache.

The following code demonstrates how to create a field that calculates a 10% sales tax for values in the "Sales" field.

The resulting PivotTable report is shown in the image below.

Expanded Modify a Calculated Field

To change a formula for a calculated field, get access to the required field by its name or index in the PivotCalculatedFieldCollection collection and then assign a new formula to the field's PivotField.Formula property. To rename a calculated field, use the PivotField.Name property.

Expanded Remove a Calculated Field

To remove a particular calculated field from the collection, use the PivotCalculatedFieldCollection.Remove or PivotCalculatedFieldCollection.RemoveAt method. To remove all calculated fields from the collection at once, use the PivotCalculatedFieldCollection.Clear method.

Is this topic helpful?​​​​​​​