06.03 Cross Dimensional Expressions
Overview
Cross‑dimensional calculations allow you to reference items from other dimensions within a single term of an expression.
They extend standard calculations by letting you “look across” dimensions to pull in specific values such as:
A particular account
A specific division or department
A specific period, region, entity, or indeed any other dimension
Here is a video, or read below.
A calculation becomes cross‑dimensional when any term in the expression includes members from more than one dimension.
There is no limit to how many dimensions you can stack in a single term.
Note: This is not the same as a conditional calculation.
A cross‑dimensional expression runs for every cell affected by the calculation.
It simply uses fixed dimensional references as part of the formula.
Example Scenario
Goal: Show each account as a percentage of Sales
Suppose you want a new column in your P&L that displays, for each row (ie: each account nominal), how that account compares to the Sales account expressed as a percentage.
For example:
Cost of Sales might be 45% of Sales
Marketing Expense might be 12% of Sales
etc.
We’ll create a calculation that does exactly that, using a cross‑dimensional reference to the Sales nominal account.
Step 1 — Choose the Correct Dimension
We want our new metric to appear as a column next to Actuals.
Since “Actuals” belongs to the Measures dimension:
The new calculation must also be created in the Measures dimension.
Name it something like PctSales (Percentage of Sales).
It will appear as a new Measure member.
Step 2 — Create the Expression
Important rule:
The dimension you create the calculation in must be the first dimension referenced in the first term of the formula.
This tells FastClose where the calculation “lives”.
So we begin with our first term:
[Measures].[Balance]
This becomes the numerator: the balance for whatever account is on the row.
Next, we're going to divide by a second term, the balance [Measures].[Balance] of the Sales account [Segment1].[Sales], which we can see is a different dimension (Account, or Segment1, depending on the naming), giving us our second term:
[Measures].[Balance][Segment1].[Sales]
giving us the complete expression
[Measures].[Balance] / [Measures].[Balance][Segment1].[Sales]
What each term means
[Measures].[Balance]
Retrieves the balance value for the current row’s account.
(This part is not cross‑dimensional.)[Measures].[Balance][Segment1].[Sales]
Retrieves the balance specifically for the Sales account, regardless of what row the report is currently on.
(This is cross‑dimensional because two dimensions are referenced: both Measures and Segment1)
The result is a PctSales column that compares each account against Sales.
Cross‑Dimensional Expressions Can Use Many Dimensions
You are not limited to crossing just one additional dimension.
FastClose allows you to stack multiple dimension filters in a single term.
Example:
Percentage of Sales for Division A, and further restricted to Department X.
Starting with the earlier expression:
[Measures].[Balance] / [Measures].[Balance][Segment1].[Sales]
we could add a Division item A:
[Measures].[Balance] /[Measures].[Balance][Segment1].[Sales][Segment2].[Division A]
then add a Department item X:
[Measures].[Balance] /[Measures].[Balance][Segment1].[Sales][Segment2].[Division A][Segment3].[Department X]
Explanation of the stacked terms
[Measures].[Balance]
The current row’s balance, unchanged.[Segment1].[Sales]
Lock the denominator to the Sales account.[Segment2].[Division A]
Narrow the value to only Sales within Division A.[Segment3].[Department X]
Narrow further to Sales for a specific department.
This allows extremely precise metrics such as:
“% of Sales in Division A”
“% of Sales in Department X within Division A”
“% of last year’s Sales for Entity B in Region West”
…all with a single expression.
Why Cross‑Dimensional Expressions Are Powerful
They allow you to:
Compare any metric to a fixed target (e.g., Sales, Gross Margin, Headcount).
Build ratios, percentages, KPIs, and benchmarks without restructuring your report.
Target specific slices of data across multiple dimensions simultaneously.
Analyse relationships between different parts of the chart of accounts or organisation.
And all of this works within one calculation, automatically applied across the entire report.
Exercises and Tutorial Survey
Now try the downloadable exercises below:
06.03 Advanced Calculations - Cross Dimensional Expressions - Exercises.pdf
Your feedback helps us do better, to fill in a short survey on this tutorial, click here.
