Table of Contents

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 totalsSelect the table containing the totals/subtotals.
Column with totalsSelect the column in the selected table containing the total/subtotal values.
Columns with weightsSelect the column in the current table with the values that will be used to generate proportions from.
GroupingSelect 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

LocationMonthSales
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

CityGoal
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")


LocationMonthSalesGoal
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.