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.

EasyMorph Help

Table of Contents