Skip to main content
Skip table of contents

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.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.