Break-down Data by Dimensions
This page gives an overview of 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 allocate cost to each product we need a distribution that we can multiply by cost. Its easily calculated. We just divide each product's 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 remember that the distribution adds up to 1 for 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")
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 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.