06.02 Conditional Calculations

Overview

Conditional calculations allow you to refine or replace the default behaviour of a calculation based on specific criteria; they build on simple calculations by adding one or more override expressions that apply only when certain dimensional conditions are met.

Put another way:

This makes conditional calculations invaluable for handling real‑world reporting logic such as sign flips, alternate formulas, exceptions, data clean‑ups, and more.

Here is a video, or read below.

A Simple Example: Flipping Revenue Signs

Consider a P&L report where you want all values to appear as positive numbers, regardless of debit/credit behaviour in the ERP.

You could create a new calculation called Display Amount:

Default Expression

Displays the standard ERP Actuals value:

[Measures].[Balance]

Conditional Override

For a specific range of account codes (e.g., 4000–4999 for sales revenue), we override the default and instead multiply the value by –1:

[Measures].[Balance] * -1

In the calculation editor, conditional calculations are marked with a special symbol

to indicate that override rules are present.

What This Produces

The result is a P&L where:

• All revenue accounts (4000–4999) show positive values (because they were flipped)

• All other accounts still show their normal values

A clean, readable report — without changing the underlying data.

Using Conditionals to Restrict Where an Expression Runs

A very common use case is when a calculation should only apply in a small subset of cells, a specific zone of the grid.

The pattern is always the same:

1. Leave the default expression blank, and

2. Put your desired logic (expression) into an override rule (set of conditions).

This creates targeted, precisely controlled calculations without affecting the rest of the dataset.

Examples:

• Apply a formula only for a certain account, division, or currency

• Apply logic only to YTD values, not periodic values

• Restrict an expression to a single measure (e.g., “Actuals only”)

Using the previous example then, lets say then that we want to only show credits and not debits. To do this we remove the default expression altogether in effect causing only the inverted credits to come through as below:

So only the override expression runs, and only for the dimension members specified, in this case we only wanted to show the sales (credits), inverted to positive:

• No default expression = no debits displayed

• Override applies only to sales account ranges

• The result is a column showing positive sales values only

Common Real‑World Uses

Conditional calculations are frequently used for business accounting scenarios, such as:

• Currency conversions
  Apply different exchange rates depending on the transaction currency or entity.

• Sign handling
  Flip credits, revenue, or liabilities so they display as positive.

• Data exceptions / Business‑specific adjustments
  Apply special logic only to unusual account groups or specific reporting periods.

  Reflect internal reporting rules that don’t match the ERP’s native behaviour.

Conditional calculations allow you to implement all of these without restructuring your dataset.

Exercises and Tutorial Survey

Now try the downloadable exercises below:

06 Advanced Calculations - Conditional Calculations - Exercises.pdf

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