Percent of Parent Calculation

Jason_Picker
4 yrs ago
2replies
Answered

How do you create a % of parent calculation in script mode?

Office Hours Question

- Jason_Picker
- 4 yrs ago
- Reported - view
The basic concept for calculating a percentage of parent is to place the current or selected member's value in the numerator and the parent's value in the denominator. Pyramid refers to the values as Data Points (called tuples in MDX). These functions can be created through the UI or in Script mode. I will show you both.

In the UI, you would first create a simple X / Y calculation using the Data Point nodes. Within each data point, you would leverage Functional Selections in order to identify the selected member and the parent member. You can get access to the functional selections via the fx button in the upper left of the Select Elements panel.

To do this in script, you will need to click on the Script Mode button in the upper left of the Formulate module toolbar. The exact syntax is dependent on if your model is SQL-based or MDX-based. However, both work the same way. The only real difference is the way in which the tool represents hierarchies. The two functions you are going to need is CurrentMember and Parent*.

([measures].[Sales Detail Sales],[Products].^[Products Hierarchy].CurrentMember) / ([measures].[Sales Detail Sales],[Products].^[Products Hierarchy].CurrentMember.Parent)

*NOTE: for SQL-based models, you will need Pyramid 2020.10 or higher for this to work. For MDX-based, it will work with any version of Pyramid 2020.

Here is an example of what the final product might look like:

You will notice that the percentages for each subcategory are based on their parent category. I included a percent of total for comparison as well.
- Senior Director of Product
- Ian_Macdonald
- 4 yrs ago
- Reported - view
To add to Jason's informative post above, there are a couple things to be aware of when working with SQL hierarchies in this way.

First, the hierarchy must be defined as type "Regular". By default, SQL based model hierarchies are defined as "Drill Path". Regular SQL hierarchies were introduced with Pyramid 2020.00, which is why the "Drill Path" hierarchies are the default for backward compatibility.

Secondly, for the % Parent to be calculated at the top level, this case Product Categories, there has to be a parent value defined. In Pyramid 2020.10, support for the definition of an [All] level is supported. In the case of our Product Hierarchy above, this would be the Aggregation of the top level members, Bikes, Accessories, Clothing, Components.

You define the Hierarchy Type and inclusion of an [All] level in the Model App:

With regard to the Script, Jason has used the "dot" notation to call the CurrentMember and Parent functions. This has the advantage of using fewer bracket pairs and so can make the script a little easier to read and understand.

If you double click on the CurrentMember or Parent function from the function tree panel, it will insert the functions using the Bracket notation. Thus the full expression would be:

(CurrentMember([Products].^[Products]),[measures].[Sales Detail Sales]) / (Parent(CurrentMember([Products].^[Products])),[measures].[Sales Detail Sales])

Hope that helps.

Ian