Performing Math with Compound Measures

Performing Math with Compound Measures

How to perform math against pre-defined measures

When to use

Use when you want to build up more complex equations by re-using your portable measure formulae

Instructions

Basic Syntax:

• There is no need to preface a measure with a table name when referring to it
• We never wrap measures in any aggregation function (SUM, MAX, etc…) as the measure already contains the aggregation function in its signature

Performing math on a single measure:

• Bonus Range = [Budget]*1.05
• Minimum Goal = [Budget]*0.75
• Next Week = [Current Date] + 7

Performing math against multiple measures:

• Variance = [ Budget] - [Actual]
• Avg Sales = [Sales $]/[Quantity]

Hints & tips

• When dividing a measure or value by another, it is a good idea to wrap it in the DIVIDE() function.  This function lets you return an alternate if the test returns an error by dividing by zero
• When using DIVIDE it is a good idea to return a BLANK() instead of 0, as this suppresses results with no values at all
• Average Sales = DIVIDE([Sales $],[Quantity],BLANK())