Calculated Measures

You can create your own calculated measures.

Quick Start

1. Start adding a measure using the Content Editor

2. Instead of selecting a measure existing in the cube, click the "Calculation" toggle to start creating a new measure:

   

3. Give you measure a name

4. Create a formula, you have multiple options

5. Update the proposed Formatting if desired

6. Submit

Options

You can choose from several options for creating a formula:

• Custom is the default option and offers you to write an MDX expression,
• Other options provide you with a wizard to create a calculation.

Continue reading to learn more about the formula creation options.

Custom calculation

To create a new custom calculation, you can type an MDX expression. In this example, the value as of one date is subtracted from the value as of another date.

1. Give your calculated measure a name. The name will be displayed as a column header for the new measure.
2. Type an MDX expression defining the desired calculation
3. Apply Formatting
4. Click Submit

The Calculation Editor defines a calculated member using MDX and adds its definition into the query. For simple operations, such as subtracting one measure from another or dividing a measure by a number, you can use the basic operators +, -, /, *. For more advanced calculations, check some resources to learn MDX.

Distinct count

This calculation will count the distinct occurrences of a particular level member. For example, a measure for the number of trades can be created as follows:

To create a "Distinct count" calculation:

1. Give your calculated measure a name. The name will be displayed as a column header for the new measure.
2. Select the type of calculation Distinct count.
3. Select a level at which to count the members, for instance "TradeId"
4. Click Submit.

The pseudo-code for the MDX calculation is:

Count(
  Descendants(
    [Dimension].[Hierarchy].CurrentMember,
    [Dimension].[Hierarchy].[Level]
  ),
  EXCLUDEEMPTY
)

Percentage of total

This calculation allows you, for a given measure and along a given hierarchy, to compute percentage contribution of a subtotal into a grand total. For example, a measure showing % contribution of each desk into the top-of-the-house capital requirement can be created as follows:

To create a Percentage of Total calculation,

1. Give your calculated measure a name. The name will be displayed as a column header for the new measure.
2. Select the type of calculation Percentage of Total.
3. Select a measure and a hierarchy on which to perform the calculation.
4. Click "Submit".

The pseudo-code for the MDX calculation is, given a measure A and a hierarchy [X].[Y]:

[Measures].[A] / (
    [X].[Y].[ALL].[AllMember],
    [Measures].[A]
  )

Or, an equivalent code:

(
    [X].[Y].CurrentMember,
    [Measures].[A]
)
  /
(
  [X].[Y].[ALL].[AllMember],
  [Measures].[A]
)

Percentage of parent

This calculation allows you, for a given measure and a long a given hierarchy, to compute percentage contribution of a subtotal into a parent subtotal.

To create a Percentage of parent calculation,

1. Give your calculated measure a name. The name will be displayed as a column header for the new measure.
2. Select the type of calculation Percentage of parent.
3. Select a measure and a hierarchy on which to perform the calculation.
4. Click "Submit".

The pseudo-code for the MDX calculation is:

[Measures].[A] / (
    [Dimension].[Hierarchy].CurrentMember.Parent,
    [Measures].[A]
  )

Managing Calculated Measures

Publish

If you'd like to reuse the same calculation in multiple widgets, it is recommended that you publish it to the server. Based on the permissions defined, the calculation will then become visible to your colleagues as a measure in the Data Explorer.

To publish,

• Right click on the column header of the calculated measure → select Calculated Measure → and then Publish.

OR

• in the Content Editor find the "Save" icon next to the calculated measure:

Both the above approaches bring you to the Publish popup:

In this popup you can:

1. Redefine the calculated measure name
2. See what cube it refers to, e.g. EquityDerivativesCube
3. Choose a Folder (optional)
4. Assign editor rights in the owners field
5. Assign readers to limit visibility

When published, you can use the new measure like any other measure in a cube. You can view it in the Data Explorer, select it in the Content Editor or even build another calculation on top of it!

Delete

To remove a published measure, find it in the Data Explorer, right-click then select "Delete" (Deleting requires that you have owner privileges).

Editing

A published calculated measure cannot be edited, you will have to re-create it.

To edit a calculation,

• Right click on Column header → select Calculated Measure → and then Edit.

or

• in the Content Editor find pencil icon next to the Calculated Measure:

Understanding calculation evaluation

A Calculated Measure will be evaluated at the very last moment, after all filters are applied. This means that a calculation, whose expression depends on a specific member, will return a different result, depending on whether that member will be included or excluded by the filters.

Example: Consider a calculated measure called "Desk A Count" which returns the number of records for DeskA. This calculation would be defined as:

WITH
 Member [Measures].[Desk A Count] AS (
  [Measures].[contributors.COUNT],
  [Booking].[Desk].[Desk].[DeskA]
), FORMAT_STRING = "#,###.##"

This calculation will always return the same value for all cells (number of records for DeskA), independently of the axis members.

However, as the calculation is evaluated last, filters will be taken into account while evaluating the expression. Therefore if the view is filtered by DeskB, the measure will return null as there are no more DeskA contributors.

This kind of limitation is typically encountered for day-to-day difference calculated measures.