How to: Calculate Multiple Custom Totals with Custom Summary Type

  • 8 min to read

The following example demonstrates how to calculate and display multiple Custom Totals for a field.

In this example, two Custom Totals are implemented for the Sales Person field. The first one displays a median calculated against summary values, while the second one displays the first and third quartiles.

To accomplish this task, we create two PivotGridCustomTotal objects and set their summary type to FieldSummaryType.Custom. We also assign the Custom Totals' names to PivotGridCustomTotal.Tag properties to be able to distinguish between the Custom Totals when we calculate their values. Finally, we add the created objects to the Sales Person field's PivotGridField.CustomTotals collection and enable the Custom Totals to be displayed for this field by setting the PivotGridField.TotalsVisibility property to FieldTotalsVisibility.CustomTotals.

Custom Total values are actually calculated in the PivotGridControl.CustomCellValue event. First, the event handler prepares a list of summary values against which a Custom Total will be calculated. For this purpose, it creates a summary datasource and copies the summary values to an array. After that, the array is sorted and passed to an appropriate method that calculates a median or quartile value against the provided array. Finally, the resulting value is assigned to the event parameter's PivotCellValueEventArgs.Value property.

<Window xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:dxb="http://schemas.devexpress.com/winfx/2008/xaml/bars"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        x:Class="DXPivotGrid_MultipleCustomTotals.MainWindow"
        Height="600" Width="800"
        Title="Main Window">
    <Grid>
        <dxpg:PivotGridControl CustomCellValue="pivotGridControl1_CustomCellValue"
                               Name="pivotGridControl1">
            <dxpg:PivotGridControl.Groups>
                <dxpg:PivotGridGroup Name="group1"/>
                <dxpg:PivotGridGroup Name="group2"/>
            </dxpg:PivotGridControl.Groups>
            <dxpg:PivotGridControl.Fields>
                <dxpg:PivotGridField Name="fieldSalesPerson" FieldName="Sales Person" Area="RowArea"
                                     Caption="Customer" Group="{Binding ElementName=group2}"/>
                <dxpg:PivotGridField Name="fieldYear" FieldName="OrderDate" Area="ColumnArea"
                                     Caption="Year" GroupInterval="DateYear"
                                     Group="{Binding ElementName=group1}"/>
                <dxpg:PivotGridField Name="fieldMonth" FieldName="OrderDate" Area="ColumnArea"
                                     Caption="Month" GroupInterval="DateMonth"
                                     Group="{Binding ElementName=group1}"/>
                <dxpg:PivotGridField Name="fieldCategoryName" FieldName="CategoryName"
                                     Area="RowArea" Caption="Category"
                                     Group="{Binding ElementName=group2}"/>
                <dxpg:PivotGridField Name="fieldQuantity" FieldName="Quantity" Area="DataArea"/>
            </dxpg:PivotGridControl.Fields>
        </dxpg:PivotGridControl>
    </Grid>
</Window>