SAP Analytics Cloud for Planning: Leveraging calculated & conversion measures in the New Model

GENERAL CONSIDERATIONS

The introduction of the New Model type is bringing a new set of capabilities when designing planning applications.

Not only models can now have multiple value fields (also known as key figures or measures) defined as so many measures (see example below), but these measures can be used for calculations or currency translation.

However, one must bear in mind that a Calculated Measure’s objective is ultimately to perform a cross-column calculation, i.e. calculating measures using other measures. Formulas can however use conditional statement so that, as an example, different versions can be subject to different calculations, but in a general way, a calculated measure is not an alternative to an account formula. Even further, calculated measures are not designed to run different sets of calculations for different accounts.

The design as illustrated above is not supported. The Revenue calculation should only be achieved through account or advanced formulas.

More generally speaking, considering the intrinsic complexity of the account dimension, which bears several aggregation methods and exception aggregations, it is impossible to leverage account members into a calculated measure as this would lead to tremendous complexity from an aggregation and calculation priority perspective.

CALCULATED MEASURES

Overview

As stated, calculated measures offer a column-to-column calculation paradigm, using a calculation expression. The syntax of these formulas is analog to the syntax used in Account formulas.

What’s more, in order to ease the creation of such formulas, an editor allows to use a set of functions and provides color coding based on the nature of the operands, whether they are dimensions or measures, together with an inline assistant.

Creating calculations with models that have NO account dimension

Planning models leveraging the New Model type (since release 2021.Q2) no longer need to hold an account dimension. This is particularly interesting for lines of business outside of the LOB Finance, where planning requirements can exist for data that is not typical to the accounting world. As an example, Human Resources data can be broken down by number of employees, which in turn are broken down by Opening number of employees, New Hires, Leaves, Terminations, etc… In this case, the total number of employees can be recalculated with all the period’s movements.

Applying different calculations to different dimension members

In case some calculations are only applicable to some specific data, conditional statements allow to filter a calculated measure based on different pieces of master data :

In the illustration above, CalcMeasure1 is only applied to the ‘FRANCE’ region from the org dimension.

CONVERSION MEASURES / CURRENCY TRANSLATION

One of the most interesting addition to the New Model type is the ability to manage currencies as measures.

The benefits of this new capability are numerous:

• Translated amounts can be displayed in stories side by side with local currency
• There is an unlimited amount of conversion currencies which can be displayed
• Currency translation happens in real time
• All the relevant currencies of a planning model are stored with a highly optimized storage under the hoods which ensures optimal performance when querying values in any currency

As shown below, currencies are created in the form of conversion measures. These measures need to be assigned a source measure (i.e. where to read the local currency values from) and a target currency.

When it comes to financial models, a conversion measures can also point to a Rate Type, more specifically Average or Closing rate.

Last, in the case of financial models holding both Profit &Loss as well as Balance Sheet figures, the conversion routine will also apply the Rate Type property assigned to individual accounts in order to figure whether average or closing rate is applicable.

Please make sure you have set a rate type as well as the ‘Units&Currencies’ field to currency on your Account type dimension.

Last, in order to assign a measure to represent local currency amounts for all entities, regardless of their currency, you shall create a measure with a reference to the organization dimension.

Please note that there is no need to assign a value to the Rate Type property above, in case this property is duly filled in the account dimension. Last but not least, make sure, in this case, that within Model Preferences, Structure Priority is set to ‘Account’ and not Measures. This will tell the currency conversion engine to look for an appropriate rate type in the account dimension (instead of the local currency measure definition). Obviously, in the case of models where all accounts shall use one single rate type, the previous statement can be ignored and rate type can be derived from the conversion measure.

TRANSACTION CURRENCY

Please refer to this blog by Pravin Datar on how to manage amounts in multiple currencies very seamlessly.

CONCLUSION

Calculated measures are really about performing columnar calculations and not a replacement to account formulas. Besides, they allow great flexibility to support currency conversion and transaction currency support.

USEFUL LINKS

• Introducing SAP Analytics Cloud for Planning
• SAP Analytics Cloud Learning Tracks
• SAP analytics Cloud Webinars
• SAP Analytics Cloud for Planning Community