Volume Hierarchy Percentage Calculation using SAP Analytics Cloud for Planning
this is Filippo, I’m SAP Planning Solution Architect and I worked with both BPC and SAC.
In this blog, I would like to show a basic data action: how to calculate the Volume measure percentage under all Hierarchy levels in a Product dimension inside SAP Analytics Cloud.
Let’s start with the model:
The Model has 5 input measures:
Volume is the measure that is going to be used to store the quantity.
H1PAGG, H1PPERC, H2PAGG, and H2PPERC are the measures to be used to store the aggregations and the percentages.
In this model, we have Product and Business Unit, with an Account dimension that contains a Volume Account.
The Product has the below structure:
As you can see, under every total there is a _TOT member. This member will be used to calculate and store the total for that level.
Let’s build now a simple input story and create some volumes for all the products:
As you can see, the volumes are rolling up on the BAG and BOX members, and from there to the TOT member.
Now let’s calculate the _TOT MEMBERS and store them under the H1PAGG and H2PAGG measures.
These two properties will tag the total members to be calculated, which will mimic the two levels of the H1 Hierarchy:
As you can see, the script calculated the H1PAGG and H2PAGG measures: they are aggregations according to the hierarchies.
After this calculation, the script calculates the H1PPERC and H2PPERC, which are the percentages of the volume under the first and second levels of the hierarchy.
For validation purposes, we can see that the combination of H1PAGG and HP2AGG is matching with Volume at TOT Level (Volume = 145).
H2PPERC is representing the percentage of each product volume compared to the total, that is 1.
H1PPERC is representing the percentage of each product volume compared to the second-level hierarchies. Since we have two intermediate levels of aggregation, the total is 2.
Thank you so much for reading this blog, please ask any questions in the comment if you need further detail on this SAC data action calculation, and feel free to share feedback.
If you liked this blog, follow me at Filippo Naggi
Stay tuned for interesting SAP Analytics Cloud out-of-the-box deep dive blogs.
An interesting alternative, that can be persisted, to using GrandTotal formula, that can be used at runtime.
Thanks for the insights.