Intellicus Enterprise Reporting and Business Insights 19.1

Calculated Measures

0 views June 28, 2020 0

A calculated measure is one whose value is calculated at run time using a Multidimensional Expression (MDX) that you specify when you define the calculated measure.

Calculated Measures are expressions created on existing field based measures.

Calculated measures are NOT calculated during cube build and they don’t consume any disk space.

Calculated Measures are evaluated at the time of slicing and dicing on the result set extracted for current view and analysis.

If you can achieve a measure using an expression, it is always efficient to make it a calculated measure. For example: Field1 + Field2, % of contribution etc.

Calculated measures are evaluated in the increasing order of “Solve Order” value.

Expression Syntax

The basic syntax supported is MDX linear syntax.

Measure values at the same level are accessed by

[Measures].[Field1]

Linear calculation

To Sum field1 and field2 in a calculated measure field3, do the following:

Select Field3 measure -> Check Expression in properties tab.

In the Expression box, type:

[Measures].[Field1] + [Measures].[Field2]

Previous Row Value

To calculate Running Total of field1 in a calculated measure field3, do the following:

Select Field3 measure -> Check Expression in properties tab.

In the Expression box, type:

 [Measures].[Field1]

+ ( [Measures].[Field3], prevRowMember() )

Contribution calculation – inner most group

To find Contribution of field1 in field1’s lowest grouped total, in a calculated measure field3, do the following:

Select Field3 measure -> Check Expression in properties tab.

In the Expression box, type:

 (( [Measures].[Field1] )  /

  ( [Measures].[Field1], currRowMember().PARENT ))

Contribution calculation – outer most group

To find Contribution of field1 in field1’s lowest grouped total, in a calculated measure field3, do the following:

Select Field3 measure -> Check Expression in properties tab.

In the Expression box, type:

(( [Measures].[Field1] )  /

 ( [Measures].[Field1], currentDim().defaultMember ))