05.03 Cross Dimensional Expressions

A cross-dimensional calculation is an advanced type of calculation that allows users to analyse data by focusing on a particular item from another dimension, and do it, within a single formula. It is possible to extend the focus to items in further additional dimensions.

A calculation is said to be cross dimensional if any one term in the expression includes criteria from more than one dimension. There is no limit to the number of dimensions that can be crossed in a term.

It is important to note that this is not the same as a conditional calculation, this expression will still fire for every cell effected by the calculation.

Lets look at this through an example: Say we want to calculate what any one account nominal is, as a percentage of the sales account in a P&L. We’re going to display this by creating a new column in the report that shows a number in each row, representing the value for the account on that row, relative to the sales account value, as a percentage.

Steps to Achieve This:

1. 1.      Determine the Dimension for the Calculation:

2. In this case, we want the calculation to sit next to the “Actual” column in the grid. Since the “Actual” item is in the Measures dimension, the calculation should also be created in the Measures dimension. This new metric could be called PctSales (percentage of sales) and would then appear as a new member in that dimension.

3. 2.      Create the Calculation:

An important rule of cross-dimensional calculations is that the dimension you are creating the calc in, (in this case the Measures dimension) must be the first referenced in the first term of the calculation. This allows FastClose to understand the context of the data.

So in this expression, it’s important that the first item references the Measures dimension.

Now the expression for this is:
[Measures].[Balance] / [Measures].[Balance][Segment1].[Sales]

The first term [Measures].[Balance] indeed references the Measures dimension and in so doing, ensures that the resulting item will be placed in the Measures Dimension.

This is what the two terms in the expression above mean:

• [Measures].[Balance] represents the total balance of any account. This term is not cross dimensional.

• [Measures].[Balance][Segment1].[Sales] represents the total balance, for specifically the "Sales" account nominal and as it mentions more than one dimension, is cross dimensional

Doing this, gets us the following result:

This method of referencing different dimensions within a single formula is what makes cross-dimensional calculations so powerful in FastClose. You can easily create comparisons, percentages, and other metrics targeting various data points in your system.

Cross Dimensional Calculations are not limited to operating across two dimensions though

It is straightforward to extend the term in this cross-dimensional calculation by stacking other dimensions beyond the first.

For example, to calculate the percentage of sales within a specific division or department, you simply add the relevant dimension members to the previous formula.

Steps to Achieve This:

1. Take the previous formula:

   [Measures].[Balance] / [Measures].[Balance][Segment1].[Sales]

2. Extend the Formula

   If you now wanted to calculate sales for Division A within a particular department, say Department X,

   you could extend the formula first to here:

   [Measures].[Balance] / [Measures].[Balance][Segment1].[Sales][Segment2].[Division A]

   and then to here:

   [Measures].[Balance] / [Measures].[Balance][Segment1].[Sales][Segment2].[Division A] [Segment3].[Department X]

How It Works:

• [Measures].[Balance] the balance from the Measures dimension, as before.

• [Segment1].[Sales] the Sales account in the Account dimension.

• [Segment2].[Division A] adds a filter to narrow the calculation to a specific division.

• [Segment3].[Department X] adds another filter for a specific department.

The key concept is that FastClose allows you to keep stacking dimensions—like departments, divisions, regions, or time periods—within the same term of the formula. Doing so instructs the system to focus, on data in specific items across multiple dimensions.

Exercises and Tutorial Survey

05 Advanced Calculations - Cross Dimensional Expressions - Exercises.pdf

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