Formula Editor

When adding a measure through the Tabular View "+" button or the Pivot Table wizard, you can select the "Formula" tab.

The toggle brings you to the Formula editor.

Create a new formula

To create a new formula in the Formula Editor,

1. Give your calculated measure a name. The name will be displayed as a column header for the new measure.
2. Click on column headers to select fields you will use in the calculation or click the "Sum" sign to select them from the cube.
3. Apply Formatting
4. Click Submit

The Formula 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.

Publish Formula

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

To publish,

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

or

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

Both ways bring you to the Publish popup:

Where you can:

1. Redefine the formula name, e.g. DeskA Count
2. See what cube it refers to, e.g. EquityDerivativesCube
3. Choose a Folder (optional)
4. Assign editor rights in Owners field
5. Assign a Reader 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, add it into Wizard, add it into Tabular View, and even build another formula on top of it!

Delete Formula

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

Editing Formula

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

To edit a formula,

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

or

• in Wizard find pencil icon next to the Calculated Measure:

Understanding formula evaluation

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

Example: Let's consider a formula called "Desk A Count" which returns the number of records for DeskA. Such formula would be defined as:

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

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

However, as the formula 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.