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.
- Sales forecasting: Estimate future sales based on historical sales patterns (across regions, product lines, customer segments, etc.)
- Budget allocation: Distribute a projected annual budget across departments or projects using historical spending ratios.
- Demand planning: Forecast product or resource demand by applying historical usage patterns to future projections.
- Market share analysis: Predict figure market shares for segments or competitors using historical distribution applied to overall market growth projections.
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.
Example: Determine the proportions of the individual Sales values of the Sales total (10000) and apply to the new Target total of 50000.
| Sales | Proportion (of Total) |
|---|---|
| 2000 | 20% |
| 3000 | 30% |
| 5000 | 50% |
| Total 10000 |
Apply proportions to Target of 50000.
| Proportion | Target |
|---|---|
| 20% | 10000 |
| 30% | 15000 |
| 50% | 25000 |
| Total 50000 |
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.
Before (source table)
Table 1: 2020 Quarterly Sales
| Quarter | Sales |
|---|---|
| Q1 | 236000 |
| Q2 | 70000 |
| Q3 | 116000 |
| Q4 | 148000 |
Table 2: 2021 Sales Target
| Target |
|---|
| 600000 |
After (result table)
| Quarter | Sales | Target |
|---|---|---|
| Q1 | 236000 | 248421.05 |
| Q2 | 70000 | 73684.21 |
| Q3 | 116000 | 122105.26 |
| Q4 | 148000 | 155789.47 |
Note: Target values rounded for brevity.
Action parameters
Table with totals: 2021 Sales Target
Column with totals: Target
Column with weights: Sales (from current table, "2020 Monthly Sales")
Grouping: No grouping, break down one total value
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 showing for the top 3 cities.)
Before (source table)
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 |
Table 2: 2021 Sales Targets by City
| City | Goal |
|---|---|
| Calgary, Alberta, CA | 360000 |
| New York, NY, USA | 320000 |
| Los Angeles, CA, USA | 310000 |
After (result table)
| 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 |
Note: Goal values rounded to the nearest whole number.
Action parameters
Table with totals: 2021 Sales Targets by City
Column with totals: Goal
Column with weights: Sales (from current table, "2020 Monthly Sales by City")
Grouping: Groups defined by matching columns (Location = City)
Example #3
Project workload per salesperson when 10 additional projects are acquired, based on historical workload capacities.
Before (source table)
Table 1: Current workload capacities.
| Salesperson | Workload (# of projects) |
|---|---|
| Nino | 3 |
| Dave | 8 |
| Rosa | 6 |
| Olha | 5 |
| Marie | 2 |
Table 2: New project total
| New workload |
|---|
| 34 |
After (result table)
| Salesperson | Workload (# of projects) | New workload |
|---|---|---|
| Nino | 3 | 4 |
| Dave | 8 | 11 |
| Rosa | 6 | 9 |
| Olha | 5 | 7 |
| Marie | 2 | 3 |
Note: New workload values rounded to the nearest whole number.
Action parameters
Table with totals: Table 2
Column with totals: New workload
Column with weights: Workload (# of projects)
No grouping, break down one total value