You built your workbook with all metrics and calculations, but now the end user makes the final analysis and realize you forgot a metric! (Now you’re pissed!) According to the user, this new metric has to bring the parental participation of a measure to the total of the company or at the highest level of the hierarchy.
So now you need go back to the whiteboard to analyze what you need to change to meet this need! Perhaps you may not know, but exist a feature in RPAS can solve your problem without many impacts in your configuration.
Understanding the parental aggregation.
First of all, let us remember about concepts of multidimensional hierarchy and how it behaves in the RPAS!
The multidimensional hierarchy is composed of dimensions. Each dimension has a direct relationship with a “father”; This relationship within the RPAS, allows aggregations within the hierarchy, thereby providing a way to operate in different levels to analyze any measure.
In RPAS, aggregations are too many, but all need a parental relationship (father and son) exists within the hierarchy.
You can see more details in Where to begin working on RPAS
This feature aims to define the proportion or ranking or accumulation of another measure for a parent that is up one or more levels. This functionality facilitates the analysis of financial metrics such as sales and inventory ($ and Unit).
This functionality is available from the 13.4 version of the Fusion Client.
What are the options we have for this functionality:
- Relative percent of parent: percentage of participation in relation to the parent directly in the hierarchy; ie percentage that a child is the total of a father within a hierarchy; Applied only to a particular hierarchy;
- Absolute percent of parent: share in relation to a specific position in the hierarchies; that is, a percentage corresponding to a specific base intersection (baseint) in the hierarchies; Applied to many hierarchies;
- Ranking: ranking compared to the parent directly in the hierarchy; ie ascending or descending order as a child corresponds to the parent within a hierarchy; Applied to only a certain hierarchy;
- Cumulative Sum: cumulative sum of each part based in the specific ranking (ascending or descending); Applied to only a certain hierarchy;
- Cumulative Percent: Cumulative participation of each part based in the specific ranking (ascending or descending); Applied to only a certain hierarchy;
The Extended Measure has some limitations business and technical, for example, only can be applied to measure that have aggregation method such as TOTAL; for options as Ranking, Cumulative Sum and Cumulative Percent are read only.
How to create Extended Measure in your workbook?
The steps to create an Extended Measure in a workbook is very simple; the main issue is to understand how to get the best result this functionality. Let’s do an example:
1) Pick a measure to create Extended Measure
The first step is to pick the measure you want to create the Extended Measure; After that, you need to determine which dimensions that will be used to view the results. It’s very important to evaluate the functionality.
2) Create the Extended Measure
After picking a metric, you need to click the right button to open the options menu, and thus select the “Extended Measure” -> “Create”.
By accessing the option, we’ll have all the options that we talked before about Extended Measure. Now you must set all the arguments for creation.
3) See the measure created
After creating the measure, Fusion Client will return to the workbook and the Extended Measures created will be available in the worksheet. Thus, the user can keep working with the new metrics created.
4) Update the Extedend Measure
Well, it’s the worst part! It’s impossible in Fusion Client! After creating an Extended Measure, you can’t update its composition. The only option is to reopen the workbook and create the Extended Measure again.
How to create Extended Measure in the ConfigTools?
Besides creating the Extended Measure in your workbook, you can do it in ConfigTools and thus keep it fixed in a workbook and in the configuration. This option allows you to create the necessary relationships without having to create new metrics and rules.
The steps to create an extended Measure the ConfigTools is simple, but, again, the main issue is to understand how to get the best result this functionality. Let’s do an example:
1) Acessing the workbook
The first step is select the workbook you want to create the Extended Measure; after selecting, go to the Extended Measure tab in the upper right corner.
2) Create the Extended Measure
In the Extended Measure tab, you must click the right button to display the Add option; Click Add”, a line will appear to you, this moment you have to set the informations as measure to be used, label and type of Extended Measure and hierarchical relationship.
3) Apply in the worksheet
After creating all Extended Measure, it’s necessary to link them to a worksheet to be available to the user. To do this, you must to go to the worksheet profile metrics and associate each measure in the desired worksheet.
4) Update the Extedend Measure
Fortunately here we have an option to update the Extended Measure. The only issue is to apply a patch to update the solution.
But the Extended Measure doesn’t work for me, what I can do?
You tested all options for the Extended Measure and none option what you need about parental proportion. In this scenario, you don’t have other option, you need to create a rule able to do the calculation / ratio you need.
To illustrate, I’ll get a code recently made by a friend, Daniel Pestana de Gouveia, an example of how to solve the parental issue within the RPAS.
StockTotalPercentage = if ((count([prod].top) == 1), 1, if ((count([prod].[level1]) == 1), StockTotal/StockTotal.level([prod].top), if ((count([prod].[level2]) == 1), StockTotal/StockTotal.level([prod].[level1]), if ((count([prod].[level3]) == 1), StockTotal/StockTotal.level([prod].[level2]), if ((count([prod].[level4]) == 1), StockTotal/StockTotal.level([prod].[level3]), 0)))))
I hope you can use this feature because it’s very powerful in RPAS.