> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://help.sigmacomputing.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://help.sigmacomputing.com/_mcp/server.

# Subtotal

The **Subtotal** function returns the subtotal for an aggregate formula.

## Syntax

`Subtotal(aggregate, mode, [parameters])`

Function arguments:

* **aggregate** (required) - The aggregate formula to apply across the specified mode and parameters.
* **mode** (required) - The mode to use when calculating the aggregate formula. Mode determines which dimension(s) to use when performing the calculation.

  * General purpose modes
    * `"grand_total"` (default for visualizations and pivot tables) - Calculates the subtotal for the aggregate formula.
  * Visualization modes

    * `"color"` - Calculates the aggregate formula using only the column specified for COLOR.

    * `"x_axis"` - Calculates the aggregate formula using only the column(s) placed on the X-AXIS.

    * `"trellis_column"` / `"trellis_row"` - Calculates the aggregate subtotal using only the dimension specified for the TRELLIS COLUMN or TRELLIS ROW.
  * Pivot table modes

    * `"column"` / `"row"` - Calculates aggregate subtotal for the column or row total.

    * `"column_parent"` / `"row_parent"` - Calculates the aggregate subtotal for the column or row total, relative to the subtotal calculation for the parent dimensions.
  * Table modes
    * `"parent_grouping"` - Calculates the aggregate formula using the grouping keys from a parent grouping. This is equivalent to creating the aggregate calculation in a parent grouping and referencing it from a column in a lower grouping.
* **parameters** (optional) - Specifies how many parent dimensions to ignore. Only applicable if **mode** is one of the following:
  * `"column_parent"` / `"row_parent"`
  * `"parent_grouping"`

## Notes

* The **Subtotal** function is not available for use in datasets.
* The **aggregate** argument must be a full aggregate formula, including an [aggregate](/docs/aggregate-functions) function and its required arguments. It cannot be a reference to an existing aggregate.
* The **parameters** argument counts groupings from the current grouping up. For example, if the mode is set to `"parent_grouping"` and the table has one grouping, setting **parameters** to `2` uses no grouping as the parent, performing the calculation for the entire table. Setting **parameters** to `1` uses use the parent grouping, performing the calculation for each group in the parent grouping.

## Examples

```
Subtotal(CountDistinct([Sku Number]), "grand_total")
```

* Returns the total number of unique SKU numbers, across all groupings, `1096`. In the `PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA` dataset, there are 1096 unique SKU numbers. Because this function uses the `"grand_total"` mode, it is equivalent to the formula `GrandTotal(CountDistinct([Sku Number]))`.

![A table shows a calculated column that uses the Subtotal function to return the grand total of unique SKU numbers, returning 1,096 in each row](https://files.buildwithfern.com/sigma.docs.buildwithfern.com/b15574a4b43b05d1ab625e75b6af340f0e180d7944d559a886cdbaaad60205cd/assets/docs-images/4322c6a789bddc06b0862645ac77aac89f18bf08f311593f16939e12dba5fd32-Subtotal_grand_total.png)

```
Subtotal(CountDistinct([Sku Number]), "parent_grouping")
```

* Returns the total number of unique SKU numbers for each grouping above the current. In the example below, the table is grouped by *Product Type*. The column *Subtotal of CountDistinct of Sku Number (by Parent Grouping)* shows the total number of unique products for each parent grouping.

In the `PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA` dataset, the `Art & Entertainment` product type has `116` unique products, and so the function returns `116` for each row in that grouping. For rows that are part of a different parent grouping, the value in each row is the number of unique products for that parent grouping.

![A table shows a calculated column that uses the Subtotal function to return the number of unique products for each parent grouping, returning 116 for each row in the Art & Entertainment product type grouping, and a different value for each row in the other groupings](https://files.buildwithfern.com/sigma.docs.buildwithfern.com/e84a5e96b527fad0282e0e96ccc6ee68cb7265c0d4301aa7c97d31cfd3bd7381/assets/docs-images/0114432af66bc4ecd0a7864943d4cf7b39f79ad91d49a87be435a5d07789b7c6-parent_grouping_subtotal.png)

```
Subtotal(CountDistinct([Sku Number]), "parent_grouping", 2)
```

* Calculates the total number of unique SKU numbers for each grouping two levels above the current grouping, and returns `1096` for each row. In the example below, the table is grouped by *Product Type*. The column *Subtotal of CountDistinct of Sku Number (by Parent Grouping, 2)* shows the total number of unique products for the entire table.

![A table shows a calculated column that uses the Subtotal function to return the number of unique products for the entire table, returning 1,096 for each row, despite the table being grouped by Product Type](https://files.buildwithfern.com/sigma.docs.buildwithfern.com/cb4735c0ad0fe8f07ba4521ef06390e3bf831ef7cf55240e8be96a411604c303/assets/docs-images/e242cda06bbea40e93266854c08196ca448a533db175a8d1d739d1391a198ac5-subtotal_skip_dimensions.png)

```
Subtotal(Avg([Price]), "column")
```

* Calculates the average price for each pivot table column.

In this example, pivot table columns are grouped by the *Store Region* column, so the *Subtotal Avg Price (by Column)* shows the average price for each store region. For example, in the column for the *East* store region, the average price is `202.918453`, and so the function returns that value for each row in that column of the pivot table.

![A pivot table with columns separated by Store Region, and rows separated by product type. Each cell shows the average price for the product type and store region combination. Alongside each of those cells is a calculated dimension that uses the Subtotal function to return the average price for each column next to each cell](https://files.buildwithfern.com/sigma.docs.buildwithfern.com/2b14b0e1cb053f12a168c1663964019d0ada9c7347c737841e6598957ae8236c/assets/docs-images/2f7c247-Subtotal1.png)

```
Subtotal(CountDistinct([Order Number]), "color")
```

* Calculates the number of unique orders for the dimension used to `"color"` the chart.

In this example, the chart's x-axis groups orders by the *Store Region* column, and the chart is colored by the *Product Type* column. The *Distinct Orders (by Product Type)* line in the tooltip shows the number of unique orders for that *Product Type*, regardless of *Store Region*. For example, the `Photography` *Product Type* has `110509` unique orders in the `Midwest`, but `485916` unique orders across every *Store Region.*

![A bar chart shows the number of unique orders for each store region, colored by product type. A calculated dimension uses the Subtotal function to show the number of unique orders for each product type across all store regions in a tooltip](https://files.buildwithfern.com/sigma.docs.buildwithfern.com/a39d047f1194d681d62cb21acf990719469229842197e4a761f54eb144ba235b/assets/docs-images/1a7acb0-Subtotal2.png)

## Related resources

* [PercentOfTotal](/docs/percentoftotal)
* [GrandTotal](/docs/grandtotal)