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.
We will perform this calculation with a data action: since it is not currently possible to read from hierarchy members in data action, we will need to create two properties in the product dimension, H1P and H2P.
These two properties will tag the total members to be calculated, which will mimic the two levels of the H1 Hierarchy:
Once the properties are in SAC, we can write an advanced formula script to calculate the two hierarchy levels’ aggregate volume and the percentage of each volume under the respective parent:
Finally, we can embed a trigger in our input Story and run it.
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.
