Formula Fact

If the query doesn't include the values you need, you can add calculations in a formula in the cube.

To create a formula fact, click on the Edit Fields Icon on the button on the Home Tab > View Ribbon and choose the type of fact being "formula".

  • Type in the field name

  • Type in the field caption

  • Choose the value as "formula"

  • Type in the formula, using existing Factname.Value

Note: Fact names are case sensitive

Example: BUDGETSALES.VALUE-BUDGETCOST.VALUE for a budget margin calculation.

Select whether the are evaluated (recalculated based on the formula) or summed (the row/column is added not recalculated by formula)

Example: A % formula is generated evaluated, a margin calculation can be either.

: use Default or Select how you would like the figure displayed (absolute/percent variation, percent of column/row)

Note: Refer to Viewing Percent of column in a cube

Click Save

 

 

Valid Operators & their relative priorities:

 

Operators

Priority and Example

+ , -

8 (Highest) . Eg 10+2 gives 12 

^

7 raise to the power. EG 2^4 gives16 

/, *, div, mod, *%, /%

6

+, -, +%, -%

5

<, >, < =, > =, ==, !=

4. Comparisons return 1 if true, 0 if false

not

3

and

2

or, xor

1

eqv

0 (Lowest)

 

See Also Working with Info-Explorer