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

** 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.