# Dimension Subtotals

## Content

Dimension Subtotals allow you to display one or more subtotal calculations for row or column fields in a PivotTable. This corresponds to Excel custom subtotals and is useful when you need to show multiple subtotal results, such as Sum, Count, Average, Max, or Min, for the same dimension item.
Dimension Subtotals are different from value field aggregation. Value field aggregation controls how values are summarized in the value area. Dimension Subtotals control which subtotal rows or columns are displayed for a row or column field.
![SpreadJS PivotTable displays multiple Dimension Subtotal rows for grouped field items, allowing developers to compare several aggregation functions within the same hierarchy.](https://cdn.mescius.io/document-site-files/images/b2223940-43c2-44cf-8eda-f5ab9acd84f0/image-20260722.973a3b.png?width=600)

## Dimension Subtotal States

A PivotTable field can use one of the following subtotal states:

| State | Description |
| ----- | ----------- |
| Automatic | Uses the default subtotal behavior. |
| None | Hides subtotals for the field. |
| Custom | Displays one or more selected subtotal functions for the field. |

Dimension Subtotal settings are stored with the PivotTable field. If the field is moved out of the row or column area, the setting is retained but does not affect the PivotTable display until the field is moved back to the row or column area.

## Supported Fields

Dimension Subtotals are effective for fields in the row area or column area.

| Field Area | Effective | Setting Retained |
| ---------- | --------- | ---------------- |
| Row | Yes | Yes |
| Column | Yes | Yes |
| Filter | No | Yes |
| Values | No | Yes |
| Not placed | No | Yes |

## Supported Subtotal Functions

Dimension Subtotals support the following subtotal functions:

* Sum
* Count
* Count Numbers
* Average
* Max
* Min
* Product
* StdDev
* StdDevp
* Var
* Varp
    When multiple functions are selected, SpreadJS displays them in a fixed order:

```text
Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, Varp
```

This order is preserved across layouts and after save and load.

## Configure Dimension Subtotals

Use the `dimensionSubtotals` method to get or set Dimension Subtotal settings for a PivotTable field.
The following example enables Sum, Count, and Average subtotals for the `Region` field.

```javascript
var pivotTable = sheet.pivotTables.get("myPivotTable");
pivotTable.dimensionSubtotals("salesperson", {
    useDefault: false,
    types: [
        GC.Pivot.SubtotalType.sum,
        GC.Pivot.SubtotalType.count,
        GC.Pivot.SubtotalType.average
    ]
});
```

![SpreadJS PivotTable shows Sum, Count, and Average subtotal rows for the salesperson field after custom functions are configured with the dimensionSubtotals method.](https://cdn.mescius.io/document-site-files/images/b2223940-43c2-44cf-8eda-f5ab9acd84f0/image-20260722.17849f.png?width=400)
The following example gets the Dimension Subtotal settings for a field.

```javascript
var info = pivotTable.dimensionSubtotals("salesperson");
if (info) {
    console.log(info.useDefault);
    console.log(info.types);
}
```

To use the default subtotal behavior, set `useDefault` to `true`.

```javascript
pivotTable.dimensionSubtotals("salesperson", {
    useDefault: true,
    types: []
});
```

![SpreadJS PivotTable returns the salesperson field to automatic subtotal behavior after the dimensionSubtotals method sets the useDefault property to true.](https://cdn.mescius.io/document-site-files/images/b2223940-43c2-44cf-8eda-f5ab9acd84f0/image-20260722.accde4.png?width=400)
To hide subtotals for a field, use the `subtotalVisible` method.

```javascript
pivotTable.subtotalVisible("salesperson", false);
```

![SpreadJS PivotTable hides subtotal rows for the salesperson field after the subtotalVisible method is set to false, leaving detail and grand total results.](https://cdn.mescius.io/document-site-files/images/b2223940-43c2-44cf-8eda-f5ab9acd84f0/image-20260722.4db8a9.png?width=600)

## Layout Behavior

Dimension Subtotals use the existing subtotal structure of the PivotTable layout.
In compact, outline, and tabular layouts, Dimension Subtotals expand the subtotal position into multiple subtotal rows or columns when multiple subtotal functions are enabled.
When a field has a single Dimension Subtotal function, the subtotal can follow the field subtotal position setting.

```javascript
pivotTable.subtotalPosition("salesperson", GC.Spread.Pivot.SubtotalsPosition.top);
```

![SpreadJS PivotTable positions a single Dimension Subtotal above each salesperson group through the subtotalPosition method with the top setting.](https://cdn.mescius.io/document-site-files/images/b2223940-43c2-44cf-8eda-f5ab9acd84f0/image-20260722.a9f026.png?width=400)
When a field has multiple Dimension Subtotal functions, all Dimension Subtotals are displayed at the bottom of the group, and the function order remains fixed.
Dimension Subtotals can also generate subtotal entries for leaf fields in cases where the default automatic subtotal would not normally show a subtotal. These leaf-field subtotals are placed before the grand total.
![SpreadJS PivotTable places multiple Dimension Subtotal rows in fixed function order at the bottom of groups, including leaf-field entries before the grand total.](https://cdn.mescius.io/document-site-files/images/b2223940-43c2-44cf-8eda-f5ab9acd84f0/image-20260722.fa17e6.png?width=600)
Dimension Subtotals do not change the calculation result, title, or style of the grand total.

## Subtotal Titles

The standard Dimension Subtotal title format is:

```auto
Item Caption Function Name
```

For example:

```auto
East SumEast Average
```

When the subtotal depends on value field semantics, the title also includes the value field source name.

```auto
Item Caption Function Name of Value Field Source Name
```

For example:

```auto
East Sum of SalesEast Average of Profit
```

Function names are localized based on the current culture.

## Interaction Behavior

Dimension Subtotals follow existing PivotTable interaction behavior where possible.

| Feature | Behavior |
| ------- | -------- |
| Sort | Sorting continues to follow the field’s existing sort behavior. Value sorting uses automatic subtotal semantics. |
| Filter | Filters are applied before Dimension Subtotals are generated. After filtering, Dimension Subtotals are recalculated from the remaining items. |
| Show Data As | Show Data As calculations continue to use existing PivotTable calculation behavior. |
| Grouping | Newly created grouped fields do not inherit Dimension Subtotal settings from the original field. |
| Collapse and Expand | When a level is collapsed, Dimension Subtotals are hidden and the automatic subtotal is displayed. |
| Show Details | Show Details is not supported on subtotal cells in the row or column area. |
| Drag and Drop | Dragging a Dimension Subtotal cell moves the entire item block. The subtotal function order remains fixed. |

## Calculated Items

Calculated items and Dimension Subtotals cannot be used together in the same PivotTable.
If a PivotTable contains calculated items, Dimension Subtotals cannot be used. If a PivotTable uses Dimension Subtotals, calculated items cannot be added to the PivotTable.

## Formula and Retrieval Behavior

When formula generation is enabled for PivotTable references, normal automatic subtotal cells continue to use the existing PivotTable formula generation behavior.
Dimension Subtotal cells generate sheet range references instead of PivotTable semantic formulas. This applies to both regular Dimension Subtotal cells and leaf-field Dimension Subtotal cells.
`pivotGetData` does not support retrieving values from Dimension Subtotal cells.

## Style and Theme Behavior

Dimension Subtotals can reuse existing subtotal theme presentation for levels that already have subtotal areas.
For leaf fields that did not previously have subtotal areas, newly generated Dimension Subtotals follow the normal heading or value presentation for that level.
Custom styles depend on PivotArea matching. Automatic subtotal areas and Dimension Subtotal areas are separate PivotArea targets.

## Serialization and File Compatibility

Dimension Subtotal settings are preserved in JSON and SJS save and load workflows.
SpreadJS also imports Excel custom subtotal settings as Dimension Subtotals and preserves them when exporting back to Excel. This helps retain custom subtotal state across Excel import and export workflows instead of reducing the field to the default automatic subtotal state.

## Limitations

* Dimension Subtotals are effective only for row and column fields.
* Dimension Subtotals are separate from value field aggregation settings.
* Calculated items and Dimension Subtotals cannot be used together in the same PivotTable.
* When multiple Dimension Subtotal functions are enabled, the subtotals are displayed at the bottom of the group.
* Dimension Subtotal cells generate sheet range references instead of PivotTable semantic formulas.
* `pivotGetData` does not support Dimension Subtotal cells.