DISTRIBUTE

Category: Dimensionality & hierarchies

Overview

Description	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. It can be used to redistribute values that are not available on a certain level. An example is the assignment of OPEX to products according to their revenue. Levels can be specified to restrict the distribution to these levels; otherwise values are distributed to all available levels. Use when you want to allocate aggregated values down to more detailed level values based on a weighting node.
Notes	Values with missing partners in the `'DistributionNode'` will be evenly distributed to all values in the respective dimension level. This can potentially create a large data set in case of sparse inputs, as shown for value 2027 in the example. If the distribution is executed on a dimension with a hierarchy and the chosen distribution level is a higher level, the distribution is calculated on the lowest shared level and afterwards aggregated to the specified level.
Syntax	`DISTRIBUTE('OriginalNode', 'DistributionNode' [, "Level1", ...])`
Parameters	OriginalNode: Input node, specified using the node name in single quotes, for example `'OPEX'`. DistributionNode: Node by whose value distribution the values of the OriginalNode should be disaggregated, for example `'Revenue'`. Level: Level by which the OriginalNode should be disaggregated, that is, the level that should exist on the node after application of the function, for example `"Product"`. If not specified, all additional levels in `'DistributionNode'` will be used for the distribution.

Examples

Distribute Across All Additional Levels

This example distributes OPEX to the detailed product level using revenue as the weighting node. In 2027, the weighting node has missing partners, so the value is evenly split across all available products.

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
M	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

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

Related Functions

Function	When to use instead
REDISTRIBUTE	When allocation should happen only within defined clusters or groups instead of across the full available detail.
REBOOK	When values should be moved from one level value to another instead of being split across multiple level values using weights.