Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Spreadsheet Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
   [Expand]Shortcuts
   [Collapse]Examples
    [Expand]Files
    [Expand]Workbooks
    [Expand]Worksheets
    [Expand]Rows and Columns
    [Expand]Cells
    [Expand]Formulas
    [Expand]Import and Export Data
    [Expand]Data Binding
    [Expand]Mail Merge
    [Expand]Formatting Cells
    [Expand]Conditional Formatting
    [Expand]Group Data
    [Expand]Filter Data
    [Expand]Tables
    [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: 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]Printing
    [Expand]Pictures
    [Expand]Charts
    [Expand]Protection
    [Expand]Customization
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

How to: Create a Calculated Item

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. A calculated item is a custom item in a PivotTable field whose value is produced based on values of other items in the same field.

All calculated items added to a PivotTable field are stored in the PivotCalculatedItemCollection collection, which can be accessed using the PivotField.CalculatedItems property. Use the collection's methods to create, modify or remove calculated items.

Expanded Online Video

See the following online video to learn how to add calculated fields and calculated items to a pivot table inside the SpreadsheetControl.

Expanded Calculated Item Limitations

Before inserting a calculated item, take into account the following resrictions.

Expanded Create a Calculated Item

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

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 items in the same field where the calculated item resides. To create an item reference, use one of the approaches listed in the table below. All examples in the table refer to the pivot table shown later in this section.

    Refere By

    Description

    Example

    Item Name Refer to an item by its name in the PivotTable field (PivotItem.Caption).
    When creating a reference, you can enclose the item's name in apostrophes or omit them.
    The following example demonstrates how to sum up the first three items in the "State" field:
    =Arizona+California+Colorado
    ...or...
    ='Arizona'+'California'+'Colorado'
    Item Position Refer to an item by its position in the PivotTable as currently sorted and displayed.
    Hidden items are ignored.
    The following example demonstrates how to sum up the first three items in the "State" field:
    =State[1]+State[2]+State[3]
    Item Relative Position Refer to an item by its position relative to the calculated item containing the formula.
    Positive numbers refer to items below or to the right of the calculated item.
    Negative numbers refer to items above or to the left of the calculated item.
    If the specified position is before the first item or after the last item in the field, the #REF! error is displayed.
    The following example demonstrates how to sum up the first three items in the "State" field relative to the "West Total" calculated item:
    =State[-6]+State[-5]+State[-4]
  • 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.

The following code demonstrates how to create two calculated items to calculate total sales for each region.

Show Me

A complete sample project is available in the DevExpress Code Examples database at http://www.devexpress.com/example=T298650.

The resulting PivotTable report is shown in the image below.

Expanded Modify a Calculated Item

To change a formula for a calculated item, get access to the required item by its index in the PivotCalculatedItemCollection collection and then assign a new formula to the item's PivotItem.Formula property. To rename a calculated item, use the PivotItem.Caption property.

Show Me

A complete sample project is available in the DevExpress Code Examples database at http://www.devexpress.com/example=T298650.

Expanded Remove a Calculated Item

To remove a calculated item from the field, use the PivotCalculatedItemCollection.Remove or PivotCalculatedItemCollection.RemoveAt method. To remove all calculated items from the collection at once, use the PivotCalculatedItemCollection.Clear method.

Show Me

A complete sample project is available in the DevExpress Code Examples database at http://www.devexpress.com/example=T298650.

How would you rate this topic?​​​​​​​