transformations:subtotal
Table of Contents
TOTAL/SUBTOTALS
Category: Transform / Advanced
Description
This action appends to a dataset a new column that contains an aggregated value for the selected column, or for groupings in that column. The aggregation options are: Sum, Count, Average, Min, or Max.
Use cases
Create a "total" column to calculate a total or group sum to be used to calculate a percentage value (e.g. total value/ group value).
Action settings
| Setting | Description |
|---|---|
| New column | Enter a name for the new total/subtotal column. |
| Target column | Select the numeric column used to generate the aggregate values. |
| Mode | Select the aggregation mode. Options: Sum, Count, Average, Min, and Max. |
| Groups | Select 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
Calculate the total of sales across all regions.
Before (source table)
| Region | Sales |
|---|---|
| North | 100 |
| East | 200 |
| North | 150 |
| East | 125 |
After (result table)
| Region | Sales | Total Sales |
|---|---|---|
| North | 100 | 250 |
| East | 200 | 325 |
| North | 150 | 250 |
| East | 125 | 325 |
Action parameters
New column: Total Sales
Target column: Sales
Mode: Sum
Groups: Column region
Example #2
Calculate the average number of points scored by each team across all games.
Before (source table)
| Game ID | Team | Goals Scored |
|---|---|---|
| G01 | Maple Leafs | 4 |
| G01 | Canucks | 3 |
| G02 | Maple Leafs | 2 |
| G02 | Canadiens | 5 |
| G03 | Oilers | 6 |
| G03 | Flames | 4 |
| G04 | Oilers | 3 |
| G04 | Canucks | 5 |
| G05 | Canadiens | 2 |
| G05 | Flames | 3 |
After (result table)
| Game ID | Team | Goals Scored | Average Goals |
|---|---|---|---|
| G01 | Maple Leafs | 4 | 3 |
| G01 | Canucks | 3 | 4 |
| G02 | Maple Leafs | 2 | 3 |
| G02 | Canadiens | 5 | 3.5 |
| G03 | Oilers | 6 | 4.5 |
| G03 | Flames | 4 | 3.5 |
| G04 | Oilers | 3 | 4.5 |
| G04 | Canucks | 5 | 4 |
| G05 | Canadiens | 2 | 3.5 |
| G05 | Flames | 3 | 3.5 |
Action parameters
New column: Average Goals
Target column: Goals Scored
Mode: Average
Groups: Subtotal inside groups (Team)
Community examples
- How to do recursive loops in EasyMorph (Project; Module: Module 1; Group: Tab 1; Table: Table 1; Action position: 3)
- Joining different pairs of value equivalences into “global equivalences” (Project; Module: Group; Group: Tab 1; Table: Input;
Action position: 2) - Check a column for unique and/or duplicate values and group by non-unique values (Project; Module: Main; Group: Group 1;
Table: Identify the number of accounts; Action position: 4)
See also
transformations/subtotal.txt · Last modified: 2025/01/29 07:54 by yurii