Conditional calculations are defined in the Calculations dialog as extensions of simple calculations; the default expression to be executed is defined just like any other simple calculation but then one or more override expressions are defined which are executed instead of the default when specified criteria are met.

Lets look at a simple example for a P&L: instead of just displaying actuals as stored in the ERP, we want to multiply the sales revenues by -1 so that the numbers displayed in the P&L are all positive regardless of whether they are debits or credits.

So, we might define a new calculation called “Display Amount” which as a default, simply displays the Actuals from the ERP:

But it might then define a conditional override, that for a range of account codes, displays the Actuals multiplied by -1

Conditional calculations are denoted in the calculation screen with this symbol:

What we have defined here then is a new calculation called “DisplayAmount” whose default expression is [Measures].[Balance] with an override on the Account dimension for the range 4000 to 4999 when the expression [Measures].[Balance]*-1 should be executed instead.

Leading to a result report that might look like this:

If on the other hand we wanted to only show credits and not debits, we could remove the default expression altogether in effecting causing only the inverted credits to come through as below:

Using Conditionals to Restrict an Expression

This is another very common variety of conditional calc, where the need is to only have an expression apply to a small subset of cells.

This is done exactly as in the previous example, by restricting the expression so that it only operates on a certain zone of the dataset.

The trick to doing this is to not set a default expression at all, and the actual expression you want to evaluate is configured as an override.

Common uses for conditional calculations include:

• Applying exchange rates for different currencies to convert to a base currency

• Inverting credits so that they don’t display as negatives

Along with many other more specific scenarios caused by exceptions in the way that a company handles data in its ERP.

Exercises and Tutorial Survey

05 Advanced Calculations - Conditional Calculations - Exercises.pdf

Your feedback helps us do better, to fill in a short survey on this tutorial, click here.