{{ transformations:DistributeTotalAction.png}}
====== DISTRIBUTE TOTAL ======
Category: Transform / Advanced\\

\\ 
=====Description=====
This action breaks down a total/subtotal proportionally to a group of numbers (weights).

\\ 
=====Use cases=====
Generate a projected set of values based on a historical breakdown applied to a projected total.

\\ 
=====Action settings=====
^ Setting  ^ Description  ^
|Table with totals|Select the table containing the totals/subtotals. |
|Column with totals|Select the column in the selected table containing the total/subtotal values. |
|Columns with weights|Select the column in the current table with the values that will be used to generate proportions from.|
|Grouping|Select whether distribution will occur across defined groups, or to a single total/subtotal.  Options: //No grouping,\\ break down one total value// or //Groups defined by matching columns// (and select the matching columns in both tables).|

\\ 
=====Remarks=====
This action requires two tables:  one containing the total value(s) and the current table containing the values the weights are derived from (the proportion of the individual values to the sum of those values).  These proportions are then applied to the chosen total/subtotal value to generate the final weighted values.

With "no grouping" selected, the total column selected would have only a single value (to be broken down proportionally), while the column with the weights can have any number of values.  (See Example 1, below.)  When grouping is selected, there would be a total value for each group, along with a set of weight values for that group.  (See Example 2, below.)

The new column of weighted values appended to the dataset takes the name of the selected "totals" column.

\\ 
=====Examples=====
**Example 1:**  Based on previous-year monthly sales figures, project the monthly sales goals based on the current year's total sales target.

**Source table 1:**  2020 Monthly Sales
^Month  ^Sales ^
|January  |  236000|
|February  |  248000|
|March  |  94000|
|April  |  70000|
|May  |  83000|
|June  |  100000|
|July  |  116000|
|August  |  62000|
|September  |  65000|
|October  |  69000|
|November  |  148000|
|December  |  209000|
\\ 
**Source Table 2:**  2021 Sales Target
^Target  ^
|1800000|
\\ 
**Action parameters:**
> Table with totals is "2021 Sales Target"
> Column with totals is "Target"
> Column with weights is "Sales" (from current table, "2020 Monthly Sales")
> Grouping is "No grouping, break down one total value"
\\ 
**Result table:**
^Month  ^Sales ^Target  ^
|January  |  236000|  283200|
|February  |  236000|  297600|
|March  |  94000|  12800|
|April  |  70000|  84000|
|May  |  83000|  99600|
|June  |  100000|  120000|
|July  |  116000|  139200|
|August  |  62000|  74400|
|September  |  65000|  78000|
|October  |  69000|  82800|
|November  |  148000|  177600|
|December  |  209000|  250800|

\\ \\ 
**Example 2:**  Based on previous-year monthly sales figures, per major city, project the monthly sales goals based on the current year's total sales targets.  (Data for Jan, Feb, and Mar only showing for the top 3 cities.)

**Source Table 1:**  2020 Monthly Sales by City

^Location^Month^Sales^
|Calgary, Alberta, CA  |January  |  150000|
|Calgary, Alberta, CA  |February  |  96000|
|Calgary, Alberta, CA  |March  |  77000|
|New York, NY, USA  |January  |  120000|
|New York, NY, USA  |February  |  98000|
|New York, NY, USA  |March  |  75000|
|Los Angeles, CA, USA  |January  |  105000|
|Los Angeles, CA, USA  |February  |  100000|
|Los Angeles, CA, USA  |March  |  80000|
\\ 
**Source Table 2:** 2021 Sales Targets by City

^City^Goal^
|Calgary, Alberta, CA  |  360000|
|New York, NY, USA  |  320000|
|Los Angeles, CA, USA  |  310000|
\\ 
**Action parameters:**
> Table with totals is "2021 Sales Targets by City"
> Column with totals is "Goal"
> Column with weights is "Sales" (from current table, "2020 Monthly Sales by City")
> Grouping is "Groups defined by matching columns" ("Location" = "City")

\\ 
^Location^Month^Sales^Goal^
|Calgary, Alberta, CA  |January  |  150000|  167183|
|Calgary, Alberta, CA  |February  |  96000|  106997|
|Calgary, Alberta, CA  |March  |  77000|  85820|
|New York, NY, USA  |January  |  120000|  131058|
|New York, NY, USA  |February  |  98000|  107031|
|New York, NY, USA  |March  |  75000|  81911|
|Los Angeles, CA, USA  |January  |  105000|  114211|
|Los Angeles, CA, USA  |February  |  100000|  108771|
|Los Angeles, CA, USA  |March  |  80000|  87018|
* Goal values rounded to the nearest whole number.