Calculating Before or After

Unknown macro: {scrollbar}

Differences between Before and After for derived measures

Suppose we have two fields in a datasource: FieldA and FieldB.

We define two basic measures:

A = FieldA with aggregation SUM
B = FieldB with aggregation SUM

Now suppose that we have the following hierarchy in a dimension:

Where:

a1, a2 y a3 are the values for Measure A for the leaves in the dimension respectively
b1, b2 y b3 are the values for Measure B for the leaves in the dimension respectively

We define a derived measure:

M = A / B

Depending on the moment when the expression of M is applied (quotient), different values will be obtained.

This can be controled with the option "Calculate values before / after aggregating calculation terms"

Calculating values after aggregating calculation terms

In this example the value for measure M in the highest element in the hierarchy is obtained by aggregating A, then aggregating B and then applying the expression of M.

This means that the expression of M is applied AFTER aggregating

Calculating values before aggregating calculation terms

In this example the value for measure M in the highest element in the hierarchy is obtained as the aggregation of M in each of the children.

This means that the expression of M is applied BEFORE aggregating

Conclusions

The result is different in each case.
If we assign values for measures A and B

a1 = 20
a2 = 18
a3 = 16

b1 = 10
b2 = 6
b3 = 4

Here are the different results:

Using BEFORE
M = (20 + 18 + 16) / (10 + 6 + 4) = 2.7

Using AFTER
M = 20/10 + 18/6 + 16/4 = 9

The choice of one method or the other will depend on the semantics of the measure.

Unknown macro: {scrollbar}