# Bind Pivot Grid Fields to Calculated Expressions | WinForms Controls | DevExpress Documentation

This topic describes how to use the [Binding API](/CoreLibraries/401533/devexpress-pivot-grid-core-library/data-binding-api) to create calculated fields when the Pivot Grid uses the Optimized data processing engine.

Calculated fields display the result of calculated expressions. Each **calculated field** has a binding expression that can be a formula or an aggregate function. The expression allows you to not only obtain values from a field in the data source, but specify exactly how to calculate the data (for example, aggregate it).

[Run Demo: Calculated Fields](dxdemo://Win/XtraPivotGrid/MainDemo/CalculatedFields) 

Expressions are computed at the data source level. This means that if you specify the field in the expression, Pivot Grid uses field values from the data source. Use the [PivotGridFieldBase.Name](/CoreLibraries/DevExpress.XtraPivotGrid.PivotGridFieldBase.Name) property to specify the field. 

If you want to use aggregated field values, wrap the field in the corresponding [aggregate function](/CoreLibraries/120512/devexpress-pivot-grid-core-library/advanced-analytics/pivot-grid-expression-syntax#functions). The [PivotGridFieldBase.SummaryType](/CoreLibraries/DevExpress.XtraPivotGrid.PivotGridFieldBase.SummaryType) and [PivotGridFieldOptions.AllowRunTimeSummaryChange](/CoreLibraries/DevExpress.XtraPivotGrid.PivotGridFieldOptions.AllowRunTimeSummaryChange) properties are not in effect for a field that is bound to such an expression.

For example, the following expression is calculated based on underlying values of the Unit Price and Quantity fields:

`[Unit Price] * [Quantity]`

In case you want to calculate the average sales, use the following expression:

`Avg([Unit Price]) * Avg([Quantity])`

## Create a Calculated Field in Visual Studio Designer

Follow the steps below to create a calculated field in the Pivot Grid:

1. Add a new data field in any of the following ways:

    - Click ‘Run Designer’ in the PivotGrid’s smart tag menu. In the [Pivot Grid Designer](/WindowsForms/1825/controls-and-libraries/pivot-grid/design-time-features/pivotgrid-designer) window, select the [Fields](/WindowsForms/1826/controls-and-libraries/pivot-grid/design-time-features/pivotgrid-designer/fields-page) page and click ![AddNewFieldButton](/WindowsForms/images/addnewfieldbutton21412.png) or ![InsertNewFieldButton](/WindowsForms/images/insertnewfieldbutton21413.png) to add a new field.
    - Use one of the ‘Add Field to…’ options in the PivotGrid’s smart tag menu to add a new field to the required area directly. Then, you can specify its properties in the property grid or in the field’s smart tag menu.
2. Set the field’s [PivotGridFieldBase.DataBinding](/CoreLibraries/DevExpress.XtraPivotGrid.PivotGridFieldBase.DataBinding) property to `Expression`.
3. Specify the expression. Click the `Expression` property’s ellipsis button and enter the expression in the invoked [Expression Editor](/WindowsForms/6212/common-features/expressions/expression-editor). 

     The following expression is used as an example:

     `Not IsTotal([fieldProductName]) And FirstValue([fieldCategoryName]) = 'Beverages'`

     ![Expression data binding example](/WindowsForms/images/expression-data-binding-expression-editor-example.png)  

     For information about expression operators and functions, refer to the following article: [Pivot Grid Expression Syntax](/CoreLibraries/120512/devexpress-pivot-grid-core-library/advanced-analytics/pivot-grid-expression-syntax).

As a result, the *Is Beverages* field displays *true* if the cell value from the *Extended Price* column belongs to the *Beverages* category and is not the total value; otherwise, *false*.  

![is-beverages-field-example](/WindowsForms/images/is-beverages-field-example.png)

## Create a Calculated Field in Code

Optimized mode supports [ExpressionDataBinding](/WindowsForms/DevExpress.XtraPivotGrid.ExpressionDataBinding). 

Follow the steps below to create a calculated field in [Optimized](/CoreLibraries/401367/devexpress-pivot-grid-core-library/pivot-grid-modes/in-memory-mode/pivot-grid-optimized-calculation-engine) mode:

1. Create an [ExpressionDataBinding](/WindowsForms/DevExpress.XtraPivotGrid.ExpressionDataBinding) instance and pass the expression in its constructor as a parameter.
2. Assign the created object to the [PivotGridFieldBase.DataBinding](/CoreLibraries/DevExpress.XtraPivotGrid.PivotGridFieldBase.DataBinding) property.

The following example shows how to use `ExpressionDataBinding` to display sales by country for different years:

[Run Demo: Filter Sales by Year and Country](dxdemo://Win/XtraPivotGrid/MainDemo/CodeExamples/Intermediate_Level_Aggregations_%28Optimized_Mode%29.Filter_Sales_by_Year_and_Country)

- C#
- VB

<section id="tabpanel_lwHOsHmNB3_tabid-csharp" role="tabpanel" data-tab="tabid-csharp">
<pre><code class="lang-csharp">pivotGridControl.OptionsData.DataProcessingEngine = PivotDataProcessingEngine.Optimized;
PivotGridField field = new PivotGridField {
    Name = &quot;SalesByYearByCountry&quot;,
    Area = PivotArea.FilterArea,
    DataBinding = new ExpressionDataBinding(&quot;Aggr(Sum([ExtendedPrice]), GetYear([OrderDate]), [Country])&quot;)
};
pivotGridControl.Fields.Add(field);
</code></pre></section>
<section id="tabpanel_lwHOsHmNB3_tabid-vb" role="tabpanel" data-tab="tabid-vb" aria-hidden="true" hidden="hidden">
<pre><code class="lang-vb">pivotGridControl.OptionsData.DataProcessingEngine = PivotDataProcessingEngine.Optimized
Dim field As PivotGridField = New PivotGridField With {
    .Name = &quot;SalesByYearByCountry&quot;,
    .Area = PivotArea.FilterArea,
    .DataBinding = New ExpressionDataBinding(&quot;Aggr(Sum([ExtendedPrice]), GetYear([OrderDate]), [Country])&quot;)
}
pivotGridControl.Fields.Add(field)
</code></pre></section>

## Use Custom Functions in Expression

You can create functions with custom logic to build an expression that executes complex calculations for Pivot Grid’s field.

Refer to the following articles for more information about custom functions:

- [Custom Functions](/WindowsForms/9947/common-features/expressions/implementing-custom-functions)
- [Custom Aggregate Functions](/CoreLibraries/403494/devexpress-pivot-grid-core-library/advanced-analytics/custom-aggregate-functions)

The following example specifies a custom summary for the *First Product Sold* field. The custom summary’s expression (`FirstValue([ProductName])`) uses a custom aggregate function (`FirstValue`) to return the first product sold by a sales person in each product category.

![First Value Custom Function Example](/WindowsForms/images/win-pivot-first-value-function.png)

[View Example](https://github.com/DevExpress-Examples/winforms-pivot-grid-custom-aggregates)

- Form1.cs
- Form1.vb

<section id="tabpanel_QENkOi3DoW_tabid-csharp" role="tabpanel" data-tab="tabid-csharp">
<pre><code data-code-links="{&quot;/ (DevExpress.XtraPivotGrid)(?:;|$)/&quot;:&quot;/WindowsForms/DevExpress.XtraPivotGrid&quot;,&quot;/ (System.Windows.Forms)(?:;|$)/&quot;:&quot;https://learn.microsoft.com/dotnet/api/system.windows.forms&quot;}" class="lang-cs">using DevExpress.XtraPivotGrid;
using System.Windows.Forms;

namespace WinPivot_CustomFunctions {
    public partial class Form1 : Form {
        public Form1() {
            InitializeComponent();     
            //  ...
            pivotGridControl1.OptionsData.DataProcessingEngine = PivotDataProcessingEngine.Optimized;
            PivotGridField pivotGridField1 = new PivotGridField() {
                Area = PivotArea.DataArea,
                AreaIndex = 0,
                Caption = &quot;First Product Sold&quot;,
                FieldName = &quot;FirstProductSold&quot;
            };
            pivotGridControl1.Fields.Add(pivotGridField1);
            pivotGridField1.DataBinding = new ExpressionDataBinding() { 
                Expression = &quot;FirstValue([ProductName])&quot; };
            pivotGridField1.Options.ShowExpressionEditorMenu = true;
            pivotGridField1.Options.ShowGrandTotal = false; 
        }      
    }
}
</code></pre></section>
<section id="tabpanel_QENkOi3DoW_tabid-vb" role="tabpanel" data-tab="tabid-vb" aria-hidden="true" hidden="hidden">
<pre><code data-code-links="{&quot;/ (System.Windows.Forms)(?:;|$)/&quot;:&quot;https://learn.microsoft.com/dotnet/api/system.windows.forms&quot;,&quot;/ (DevExpress.XtraPivotGrid)(?:;|$)/&quot;:&quot;/WindowsForms/DevExpress.XtraPivotGrid&quot;}" class="lang-vb">Imports System.Windows.Forms
Imports DevExpress.XtraPivotGrid

Namespace WinPivot_CustomFunctions
    Partial Public Class Form1
        Inherits Form
        Public Sub New()
            InitializeComponent()
            &#39;  ...
            pivotGridControl1.OptionsData.DataProcessingEngine = PivotDataProcessingEngine.Optimized
            Dim pivotGridField1 As New PivotGridField() With {
                .Area = PivotArea.DataArea,
                .AreaIndex = 0,
                .Caption = &quot;First Product Sold&quot;,
                .FieldName = &quot;FirstProductSold&quot;
            }
            pivotGridControl1.Fields.Add(pivotGridField1)
            pivotGridField1.DataBinding = New ExpressionDataBinding() With {.Expression = &quot;FirstValue([ProductName])&quot;}
            pivotGridField1.Options.ShowExpressionEditorMenu = True
            pivotGridField1.Options.ShowGrandTotal = False
        End Sub
    End Class
End Namespace
</code></pre></section>

## Enable Users to Edit Expressions

You can allow users to edit or create new custom expressions in the [Expression Editor](/WindowsForms/6212/common-features/expressions/expression-editor) at runtime. To accomplish this, set the [PivotGridFieldOptionsEx.ShowExpressionEditorMenu](/WindowsForms/DevExpress.XtraPivotGrid.PivotGridFieldOptionsEx.ShowExpressionEditorMenu) property to `true` to add the **Expression Editor** command to the field’s context menu or call the [ShowExpressionEditor](/WindowsForms/DevExpress.XtraPivotGrid.PivotGridControl.ShowExpressionEditor%28DevExpress.XtraPivotGrid.PivotGridField%29) method to invoke the Expression Editor. 

Note

You cannot use the Expression Editor dialog in OLAP mode.

The following code snippet shows how to invoke the Expression editor for `pivotGridField2` on a mouse click:

- C#
- VB

<section id="tabpanel_IoerQhjiyw_tabid-csharp" role="tabpanel" data-tab="tabid-csharp">
<pre><code class="lang-csharp">private void pivotGridControl1_MouseClick(object sender, MouseEventArgs e) {
        PivotGridHitInfo info = pivotGridControl1.CalcHitInfo(new Point(e.X, e.Y));
        if (info != null &amp;&amp; info.HeaderField == pivotGridField2) {
            pivotGridControl1.ShowExpressionEditor(pivotGridField2);
        }
    }
</code></pre></section>
<section id="tabpanel_IoerQhjiyw_tabid-vb" role="tabpanel" data-tab="tabid-vb" aria-hidden="true" hidden="hidden">
<pre><code class="lang-vb">Private Sub pivotGridControl1_MouseClick(ByVal sender As Object, ByVal e As MouseEventArgs)
        Dim info As PivotGridHitInfo = pivotGridControl1.CalcHitInfo(New Point(e.X, e.Y))
        If info IsNot Nothing AndAlso info.HeaderField = pivotGridField2 Then
            pivotGridControl1.ShowExpressionEditor(pivotGridField2)
        End If
End Sub
</code></pre></section>

![_EU_ExpressionEditorInvoking](/WindowsForms/images/eu_expressioneditorinvoking118797.png)

Use the [PivotGridOptionsBehavior.ExpressionEditorMode](/WindowsForms/DevExpress.XtraPivotGrid.PivotGridOptionsBehavior.ExpressionEditorMode) property to specify the Expression Editor’s version.

See Also

[Bind Pivot Grid Fields to Data Columns](/WindowsForms/401376/controls-and-libraries/pivot-grid/binding-to-data/in-memory-mode/Optimized-Mode/bind-pivot-grid-fields-to-data-columns)

[Expression Editor](/WindowsForms/6212/common-features/expressions/expression-editor)

[Pivot Grid Expression Syntax](/CoreLibraries/120512/devexpress-pivot-grid-core-library/advanced-analytics/pivot-grid-expression-syntax)

[Pivot Grid Data Processing Modes](/CoreLibraries/403802/devexpress-pivot-grid-core-library/pivot-grid-modes)

[PivotGridField](/WindowsForms/DevExpress.XtraPivotGrid.PivotGridField)

[Bind Pivot Grid Fields to Window Calculations](/WindowsForms/403904/controls-and-libraries/pivot-grid/binding-to-data/in-memory-mode/Optimized-Mode/bind-pivot-grid-fields-to-window-calculations)