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")

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.


FAQs

Frequently asked questions can be found here

Contact 

You may contact the Valsight Customer Support via:

+49 30 46799042

support@valsight.com

Availability: Mon-Fri *, 9 AM to 5 PM (Berlin, Germany) .

*Except Public holidays in Berlin, Germany.