Reviewing a Two-Pass Calculation Example

Consider this calculation required for Profit%:

Profit % = Profit % Sales

Assume that Table 186 shows a subset of a data block with Measures and Year as dense dimensions. Measures is tagged as accounts, and Year is tagged as time. The AGGMISSG configuration setting is turned off (the default).

Data values have been loaded into the input cells. Essbase calculates the cells in which the numbers 1 through 7 appear, in that order. For example, Profit % -> Jan is calculated first; Profit% -> Qtr1 has multiple consolidation paths.

Table 186. Two-Pass Calculation Example: Data and Calculation Order

Measures -> Year

Jan

Feb

Mar

Qtr1

Profit

75

50

120

5

Sales

150

200

240

6

Profit%

1

2

3

4, 7

Note:

For information on how cell calculation order depends on database configuration, see Cell Calculation Order.

Essbase uses this calculation order:

  1. Essbase calculates the formula Profit % Sales for Profit % -> Jan, Profit % -> Feb, Profit % -> Mar, and Profit % -> Qtr1 (1, 2, 3, 4 above).

  2. Essbase calculates Profit -> Qtr1 and Sales -> Qtr1 by adding the values for Jan, Feb, and Mar (5, 6 above).

  3. Essbase calculates Profit % -> Qtr1 by adding the values for Profit % -> Jan, Profit % -> Feb, and Profit % -> Mar (7 above). This addition of percentages produces the value 125%, which is not the correct result.

    Table 187. Two-Pass Calculation Example: Incorrect Results

    Measures/Year

    Jan

    Feb

    Mar

    Qtr1

    Profit

    75

    50

    120

    245 (5)

    Sales

    150

    200

    240

    590 (6)

    Profit%

    50% (1)

    25% (2)

    50% (3)

    0% (4)

    125% (7)

  4. If you tag Profit% as two-pass in the database outline, Essbase uses the Profit % Sales formula to recalculate the Profit% values and produce the correct results.

    Table 188. Two-Pass Calculation Example: Correct Results

    Measures/Year

    Jan

    Feb

    Mar

    Qtr1

    Profit

    75

    50

    120

    245 (5)

    Sales

    150

    200

    240

    590 (6)

    Profit%

    50% (1)

    25% (2)

    50% (3)

    0% (4)

    125% (7)

    42% (8)

For information about multiple calculation passes, see Calculation Passes.