Hi,
Can anyone suggest how can we create hierarchies using dimensions list and use it & how we can input values at a total level and get it break-back down at item level?
Thanks
Solved
Creating Hierarchies with dimensions & Break-back
+1- Apprentice Author
Best answer by BrunoRodriguez
Hello
If I understand correctly your question, you have a unit - let’s say volumes - and you have a hierarchy - let’s say a 3 level hierarchy.
My suggestion is the following:
- Metric 1: Identify the volumes at level 3
- Metric 2: Aggregate them into level 2
- Metric 3: Metric 1 / Metric 2
- This will result in the % that each level 3 represents of its level 2 parent.
- Metric 4: Add an input at level 2
- Metric 5: Metric 4 * Metric 2
- This will result in the number being disaggregated accordingly into every level 3.
For level 1, you would only have to apply the same logic. I hope it makes sense!
Kind regards,
Bruno
+6- Employee
- 25 replies
- Answer
Hello
If I understand correctly your question, you have a unit - let’s say volumes - and you have a hierarchy - let’s say a 3 level hierarchy.
My suggestion is the following:
- Metric 1: Identify the volumes at level 3
- Metric 2: Aggregate them into level 2
- Metric 3: Metric 1 / Metric 2
- This will result in the % that each level 3 represents of its level 2 parent.
- Metric 4: Add an input at level 2
- Metric 5: Metric 4 * Metric 2
- This will result in the number being disaggregated accordingly into every level 3.
For level 1, you would only have to apply the same logic. I hope it makes sense!
Kind regards,
Bruno
+10- Master Author
- 25 replies
Hello
Can you please explain in a bit detail?
I am getting stuck at Metric 2 the aggregation part because if I aggregate then I cannot enter values which is a problem in Step 4. Please kindly provide an example or screenshots, for easier understanding. Thanks a lot!
+14- Master Helper
- 118 replies
Hi
Assuming you have the product dimension as follow :
To get the break back you will have the following :
Where :
Metric_1_Volume is a metric by Product and an input to determine the split key.
Metric_2_Aggregated_Volume is a metric by Product Group with the following formula :
Metric_1_Volume[BY:Product.'Product Group']Metric_3_Volume_Ratio is a metric by Product with the following formula :
Metric_1_Volume/Metric_2_Aggregated_Volume[BY:Product.'Product Group']Metric_4_Input_Cost is a metric by Product Group and an input you want to split :
Metric_5_Cost_Disaggregated is a metric by Product with the following formula :
Metric_4_Input_Cost[BY:Product.'Product Group']*Metric_3_Volume_RatioNote that the formula in Metric_2 and Metric_3 could be compute directly in Metric_5 so you end up with only 3 metric (2 for inputs and one for the final split).
Hope this helps,
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.