Differences

This shows you the differences between two versions of the page.

--- transformations:subtotal [2018/07/22 16:00] – created dmitry
+++ transformations:subtotal [2025/01/29 07:54] (current) – [Example #1] yurii
@@ Line 1: / Line 1: @@
-===== Total / subtotals =====
+{{ transformations:SubtotalAction.png}}
+====== TOTAL/SUBTOTALS ======
+Category: Transform / Advanced\\
-This action appends to a dataset a new column that contains an aggregated total for selected column, or subtotals for groups.
+\\
+=====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. \\
-**Example 1**
+\\
+=====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).\\
-Source dataset:
-^ Region ^ Sales ^
-| North  |  100 |
-| East   |  200 |
-| North  |  150 |
-| East   |  125 |
-Objective: add a column with Total Sales.
+\\
+=====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.
-Resulting dataset:
+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).\\
-^ Region ^ Sales ^ Total Sales ^
-| North  |  100 |   575 |
-| East   |  200 |   575 |
-| North  |  150 |   575 |
-| East   |  125 |   575 |
-**Example 2**
+\\
+=====Examples=====
-Source dataset:
+====Example #1====
+>Calculate the total of sales across all regions.\\
+===Before (source table)===
 ^ Region ^ Sales ^
 | North  |  100 |
@@ Line 30: / Line 38: @@
 | East   |  125 |
-Objective: add a column with Total By Region.
+===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**|
-Resulting dataset:
+===Action parameters===
-^ Region ^ Sales ^ Total By Region ^
+> New column: Average Goals
-| North  |  100 |   250 |
+> Target column: Goals Scored
-| East   |  200 |   325 |
+> Mode: Average
-| North  |  150 |   250 |
+> Groups: Subtotal inside groups (Team)
-| East   |  125 |   325 |
-**See also**
+\\
+=====Community examples=====
+  * [[https://community.easymorph.com/t//1769/1|How to do recursive loops in EasyMorph]] ([[https://community.easymorph.com/uploads/short-url/qM6kmqrDRWiEDJFljIa2u6QWre1.morph|Project]]; Module: //Module 1//; Group: //Tab 1//; Table: //Table 1//; Action position: //3//)
+  * [[https://community.easymorph.com/t//1837/4|Joining different pairs of value equivalences into “global equivalences”]] ([[https://community.easymorph.com/uploads/short-url/1TpBXIjf6r4auDHlOBejKh2rSTw.morph|Project]]; Module: //Group//; Group: //Tab 1//; Table: //Input//;\\ Action position: //2//)
+  * [[https://community.easymorph.com/t//2505/2|Check a column for unique and/or duplicate values and group by non-unique values]] ([[https://community.easymorph.com/uploads/short-url/7s9tWfHaseZ9EqSBdVmjSfGagIS.morph|Project]]; Module: //Main//; Group: //Group 1//;\\ Table: //Identify the number of accounts//; Action position: //4//)
+\\
+=====See also=====
   * [[transformations:aggregate|Aggregate]]
+  * [[transformations:runningtotal|Running total]]