User Tools

Site Tools


transformations:subtotal

TOTAL/SUBTOTALS

Category: Transform / Math


Description

This action appends to a dataset a new column that contains an aggregated total for selected column, or subtotals for groups.


Use cases

Create a "total" column to calculate a total or group percentage value with (e.g. total value/ group value).


Action settings

SettingDescription
New columnEnter a name for the new total/subtotal column.
Target columnSelect the numeric column used to generate the aggregate values.
ModeSelect the aggregation mode. Options: Sum, Count, Average, Min, and Max.
GroupsSelect whether to derive the total/subtotal value across all rows in the dataset, or grouped by selected columns.
Options: Column total or Subtotal inside groups (and select the columns to subtotal by).


Remarks

The new column will appear as the rightmost column in the dataset, by default.

The grouping aspect of the Subtotal inside groups option is case-sensitive. Values in the grouping column(s) having different cases will be calculated as unique values (e.g. not combined).


Examples

Example 1: Add a column with Total Sales.

Source table:

Region Sales
North 100
East 200
North 150
East 125


Action parameters:

New column is "Total Sales"
Target column is "Sales"
Mode is "Sum"
Groups is "Column total"


Result table:

Region Sales Total Sales
North 100 575
East 200 575
North 150 575
East 125 575



Example 2: Add a column with Total By Region.

Source table:

Region Sales
North 100
East 200
North 150
East 125


Action parameters:

New column is "Total by Region"
Target column is "Sales"
Mode is "Sum"
Groups is "Subtotal inside groups" (column "Region" selected)


Result table:

Region Sales Total By Region
North 100 250
East 200 325
North 150 250
East 125 325


Community examples


See also

transformations/subtotal.txt · Last modified: 2021/07/18 00:03 by craigt