Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
This lesson explores the syntax and practical application of adding or subtracting measures from each other, and lays the groundwork for creating more complicated mathematical combinations
Download this lesson’s related exercise files. You can download the source data files for the course from the resources section of your Lessons page.Performing Math with Compound Measures.pbix
431 KB Performing Math with Compound Measures - Completed.pbix
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.
- 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())
Lesson notes are only available for subscribers.