How to: Percentage of parent in atoti
In the post “How to explain non-additive measures” we talked about how to use “parent” and “child” data relationships in atoti to navigate data and implement your on-the-fly allocation rules. Today let’s make use of these atoti functions to define the percentage of parent calculation.
Percentage of Parent
I’m building upon the example with a multi-level hierarchy from my previous post — an e-commerce product catalogue. The same technique can be applied to a hierarchy with just 1 level — to display a percentage of grand total.
UPDATE: The GIFs and code snippets in this article are based on an older version of atoti. We have released much smoother and even more functional dashboards and widgets with the latest version of atoti. Check out this link to see the documentation of the latest version of atoti.
We use atoti.parent_value to access subtotal for Sales above the current node. Having the subtotal at the parent level, we simply divide sales by the parent’s subtotal to obtain the percentage of parent:
As of atoti version 5.0, the parameters of the parent_value function include:
- measure — subtotal for which measure to return,
- on — rollup along which hierarchy(ies) and how many levels to go up that hierarchy,
- apply_filters — should the parent_value respect or disregard filters in the current view when subtotal is calculated,
- total_value — what to return at the very root of the hierarchy.
Now that the measure is available, let’s pick it and bring it into view:
The only issue I would like to refine is the formatting:
As you can see, the sum of children’s percentages adds up exactly to one 1 for any node, as the measure shows the contribution of subcategories into a category:
Percentage of Grand Total
Let’s create a “Sales as % of Grand Total” — this measure will visualize how much each category makes out of the total sales globally across all categories. To access the grand total, we’ll use atoti.total function:
The new measure will display the contribution of a category into the total sales across all categories, i.e. “Kitchenware” makes almost 19% of total sales, mainly thanks to the subcategory “Large appliances” which brings 12 % of total sales.
What’s the difference between the parent and the grand total
For a hierarchy with just 1 level, or in other words when an attribute is a list of values, the percentage of parent and percentage of grand total are essentially the same. However, when an attribute is a tree, then you can use the atoti.parent_value function to access desired subtotals, and atoti.total to access the very root, or the grand total.
Instead of the conclusion
Thank you for reading this tutorial, I hope it’s helpful. Please reach out if you have any questions.
