DISTRIBUTE
Category: Dimensionality & hierarchies
Overview
The DISTRIBUTE function distributes the values of the Original Node based on the value distribution from the Distribution Node in relation to the aggregated values of the Distribution Node when rolled up to the levels of the Original Node.
Use this when you want to allocate aggregated values down to more detailed level values based on a weighting node.
Syntax
DISTRIBUTE('OriginalNode', 'DistributionNode' [, "Level1", ...])
Example usage: DISTRIBUTE('OPEX', 'Revenue', "Product")
Parameters
Parameter | Description | Type | Required |
|---|---|---|---|
Original Node | Input node whose values should be distributed, specified in single quotes (e.g. | Node reference | Yes |
Distribution Node | Node whose value distribution provides the weights for disaggregation (e.g. | Node reference | Yes |
Level | One or more level names to restrict which levels the Original Node is distributed to (e.g. | Level name | No |
Output Shape
Aspect | Behavior |
|---|---|
Dimensionality | Increases. The output contains all levels from the Original Node plus the distribute levels from the Distribution Node. |
Values | Each input value is split proportionally based on the weights in the Distribution Node. The total sum is preserved. |
Row count | Expands to include one row per combination of original levels and distribute level values. |
Watch Out
Values with missing partners in the Distribution Node will be evenly distributed to all values in the respective dimension level. This can create a large data set with sparse inputs.
When the sum of distribution weights is zero for a partition, the input value is distributed evenly across all non-null entries in that partition.
The Distribution Node must contain all levels of the Original Node. If it is coarser than the Original Node in any dimension, a validation error occurs.
The Original Node must not already have the distribute level. You cannot distribute to a level that already exists on the input.
If the distribution is executed on a dimension with a hierarchy and the chosen distribute level is a higher level, the distribution is calculated on the lowest shared level and afterwards aggregated to the specified level.
Filters on the distribute level's dimension are removed during calculation and applied to the result afterwards.
Examples
Distribute across all additional levels
This example distributes OPEX to the detailed product level using revenue as the weighting node.
Input node: 'OPEX'
Year | OPEX |
|---|---|
2025 | 2000 |
2026 | 4000 |
2027 | 12000 |
Input node: 'Revenue'
Product Line | Product | 2025 | 2026 |
|---|---|---|---|
M | M1 | 100 | 200 |
M | M2 | 300 | 600 |
Z | Z3 | 400 | 800 |
Formula: DISTRIBUTE('OPEX', 'Revenue')
Product Dimension | Year | |||
Product Line | Product | 2025 | 2026 | 2027 |
M | M1 | 2000 * 100 / 800 = 250 | 4000 * 200 / 1600 = 500 | 12000 / 4 = 3000 |
M2 | 2000 * 300 / 800 = 750 | 4000 * 600 / 1600 = 1500 | 12000 / 4 = 3000 | |
Z | Z3 | 2000 * 400 / 800 = 1000 | 4000 * 800 / 1600 = 2000 | 12000 / 4 = 3000 |
X | X4 | - | - | 12000 / 4 = 3000 |
Totals | 2000 | 4000 | 12000 | |
For 2025 and 2026, each product's share is calculated as: OPEX * (product revenue / total revenue). For 2027, no revenue data exists, so the value is evenly distributed across all 4 products. The totals are preserved in every year.
Restrict the Distribution to Selected Levels
This example restricts the distribution to "Year" and "Product Line", so the result stays aggregated at product-line level instead of distributing down to each product.
Formula: DISTRIBUTE('OPEX', 'Revenue', "Year", "Product Line")
Product Dimension | Year | ||
Product Line | 2025 | 2026 | 2027 |
M | 2000 * 400 / 800 = 1000 | 4000 * 800 / 1600 = 2000 | 12000 / 3 = 4000 |
Z | 2000 * 400 / 800 = 1000 | 4000 * 800 / 1600 = 2000 | 12000 / 3 = 4000 |
X | - | - | 12000 / 3 = 4000 |
Totals | 2000 | 4000 | 12000 |
By specifying "Product Line" instead of "Product", the distribution stays at the coarser level. Revenue for product line M is the sum of M1 and M2 (100 + 300 = 400 in 2025). Totals are again preserved.
Related Functions
Function | When to use instead |
|---|---|
When allocation should happen only within defined clusters or groups instead of across the full available detail. | |
When values should be moved from one level value to another instead of being split across multiple level values using weights. |