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 |