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 credits 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 amend the default expression to [Measures].[Balance] * 0  in effecting causing only the inverted credits to come through.

Using Conditionals to Restrict an Expression

Where the need is to restrict an expression from operating on certain zones of the dataset, rather than override it, the trick is as above, to set the default expression to something that will evaluate to 0 (eg: [Measures].[Balance] * 0  ) with the actual expression 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.