In the post “How to explain non-additive measures” we talked about how data scientists can use “parent” and “child” data relationships in Atoti to analyze data and implement 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 and the associated sales history. The same technique can be applied to a hierarchy with just 1 level – to display a percentage of grand total.

We use Atoti.parent_value to access the 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:

cube.measures["Sales as % of Parent"] = cube.measures["Sales"] / Atoti.parent_value(
    cube.measures["Sales"], 
    {cube.hierarchies["Catalog"]: 1}, 
    apply_filters=True,
    total_value=cube.measures["Sales"],
)

As of Atoti version 5.0, the parameters of the parent_value function include the following:

• 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:

cube.measures["Sales as % of Parent"].formatter = "DOUBLE[0.00%]"

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:

cube.measures["Sales as % of Grand Total"] = cube.measures["Sales"] / Atoti.total(
    cube.measures["Sales"],
    cube.hierarchies["Catalog"]
)
cube.measures["Sales as % of Grand Total"].formatter = "DOUBLE[0.00%]"

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 parent and 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.