Break-down Data by Dimensions

This page gives an overview on how data can be distributed according to a predefined distribution or another data set. Imagine you have a data set that contains cost data and it is only available per year and you want to break it down by each product's revenue.

To achieve this, you need to multiply it by distribution per product and year, which needs to be obtained from the revenue.

Let us consider the following example. We have 3 products and the following revenue and cost data.

Cost

Year Value
2018 100
2019 120

Revenue

Year Product Value
2018 A 60
2018 B 70
2018 C 80
2019 A 120
2019 B 130


To be able to allocate cost to each product we need a distribution that we can multiply by cost. Its easily calculated. We just divide each products yearly revenue by the yearly total revenue (of all products).

'Revenue' / ROLLUP('Revenue',"Time","Year")

ROLLUP('Revenue',"Time","Year")

Year Value
2018 60+70+80=210
2019 120+130=250

'Revenue' / ROLLUP('Revenue',"Time","Year")

Year Product Value
2018 A 60 / 210 = 0.285
2018 B 70 / 210 = 0.333
2018 C 80 / 210 = 0.381
2019 A 120 / 250 = 0.48
2019 B 130 / 250 = 0.52

Always keep in mind that the distribution adds up to 1 in each year - even in 2018 if you include all digits.

We can now multiply the cost with the weights:

'Cost' * 'Revenue' / ROLLUP('Revenue',"Time","Year")

(warning) Notice that the ROLLUP is not always done on the Year level, but always to all levels of the node-to-be-distributed, i.e. the costs.


'Cost' * 'Revenue' / ROLLUP('Revenue',"Time","Year"):

Year Product Value
2018 A 100 * 0.285 = 28.5
2018 B 100 * 0.333 = 33.3
2018 C 100 * 0.381 = 38.1
2019 A 120 * 0.48 = 57.6
2019 B 120 * 0.52 = 62.4

Note: This method assumes that the distribution can be obtained through the division, which may not always be the case. If the result of the ROLLUP does not have a non zero value for each entry in the cost node, the result may be less than what you started with, e.g. some fractions remain undistributed and are lost. For these cases check out the DISAGGREGATE function, which can be adjusted on a level by level basis.


Was this helpful?

Yes | Somewhat | No