Calculated Measures

The Calculated Measures are derived data values calculated with formulas using standard algebraic notations and functions.

M3 DMP automatically keeps the Calculated Measure updated, even when other data is updated or when the grouping levels are changed.

Time Series Functions

Calculated Measures that constrain or use other measures that have Time Series Functions are marked internally in the Grid Memory Cube as a measure that is only updated when the Grid is reloaded. M3 DMP still allows other measures to be changed and recalculates all measure that does not depend on a Time Series Function.

Basic Calculation Formulas

This table displays the operators that are allowed in Basic Calculation Formulas listed by the frequency of appearance.

Operator Description
(-) Unary minus or negation
^ Exponent
* Multiplication
/ Division
+ Addition
- Subtraction

The numeric constants are also allowed, but must always be specified with a dot (.) as decimal separator. Thousand separators or digital grouping symbols are not allowed.

M3 DMP also supports using parentheses () in formulas to overrule normal precedence of operators.

A formula can use other measures - normal measures as well as other calculated measures. You must enclose each measures used in a formula in square brackets as [measurename].

You can use numerics for keys in a calculated measure. This formula sets a fixed factor that is defined with the keys of the dataset and uses it as a conversion in computation. For example, quantity is used to compute a box size and unit volume is used to compute the number of barrels.

Functions for formulas for Conditional Calculated Measures

This table displays the Functions for formulas for conditional calculated measures.

Function Description
IIF([M1], [M2], [M3] )   if M1 <> 0 then M2 else M3
EQ( [M1], [M2] ) return 1 if M1=M2 else 0
NE( [M1], [M2] ) return 1 if M1<>M2 else 0
GT( [M1], [M2] ) return 1 if M1>M2 else 0
LT( [M1], [M2] )   return 1 if M1<M2 else 0
GE( [M1], [M2] )   return 1 if M1>=M2 else 0
LE( [M1], [M2] ) return 1 if M1<=M2 else 0

Examples:

Formula Description
Flag: GT ([M1], [M2]) Flag to see if one measure is greater than another
Greatest: IIF (GE ([M1], [M2]), [M1], [M2])) Display the greatest of two measures
Show if: IIF (GT ([M1], 1), [M1], 1/0) Display M1 if M1 > 1 else display empty cell
Display100orMore: IIF (GT ([M1],100), [M1], 100) Display M1 but minimum 100
DisplayFirstContinues: IIF (GT ([M1], 0 ), [M1], SELF(-1)) Display earlier periods for M1 unless it has changed
CM: IIF (GT ([Unit.Price], 0), [Unit.Price], SELF (-1) Display Latest Value for later periods

Stock Consumed: SELF (-1) + [Free Stock] - [Tot FCS]

StockOut: IIF (LE ([StockConsumed], 0), 1, 0)

Display when Stockout happens
Deviation = STDDEV (3, [Sales]) / [Sales] Calculate the Standard Deviation percentage
Safety = STDDEV (3, [Sales]) * NORMSINVNA (0.95) * Sqr (3) Calculate the Safety stock