Locked 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
Exercise files
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.pbix431 KB Performing Math with Compound Measures - Completed.pbix
437.3 KB
Quick reference
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())
- 00:05 In this video, we're gonna continue to process of working with measures.
- 00:08 But this time, we're gonna start using measures in other measures.
- 00:12 We're creating compound measures.
- 00:14 But to frame the discussion I'd like to review the signature of a couple of simple
- 00:17 measures that I'm gonna use for the examples here.
- 00:20 The first one is where we actually go through and
- 00:23 create in a measure called Actual.
- 00:25 Actual is a simple measure.
- 00:26 It simply takes the values in the amount column of the transactions table and
- 00:30 it summarizes all of them using the sum function.
- 00:33 Notice that the key part here is that we're using a fully qualified reference,
- 00:37 which means that we have the table name first for
- 00:39 transactions, and then the tables column, which is amounts in square brackets.
- 00:44 The two of those things together are then wrapped in the function which in this case
- 00:48 is sum, but it could easily be max or min or whatever.
- 00:51 We have another basic measures as well using the same kind of a pattern which
- 00:55 is Budget.
- 00:55 So budget is being defined as the sum of budgets amount.
- 01:00 Now, big thing I want you to remember here, fully qualified,
- 01:04 we always put in that table name first.
- 01:07 When we start reaching out to using measures and other measures or
- 01:10 using a compound measure.
- 01:12 We actually don't need to fully qualify with our table names.
- 01:16 We simply refer to the previously defined measure and
- 01:19 wrap it within square brackets.
- 01:22 So an example of this might be where we take something like budget and we say,
- 01:25 hey, you know what, I wanna add 5% to that to make a new target for
- 01:28 what my sales people should be shooting for.
- 01:30 I want them to exceed the budget numbers.
- 01:32 So I would create a new measure called Target which is equal to budget times1.05.
- 01:38 Why does this work?
- 01:39 Well, because inside Budget, if you wanna think of it this way,
- 01:42 I can actually take the original formula that's contained within the Budget measure
- 01:47 and I can swap it in place of [Budget].
- 01:49 So at that point, I would have, SUM(Budgets[Amount]) times 1.05.
- 01:54 It's the same thing, but
- 01:56 obviously referring to [Budget] is a heck of a lot less typing.
- 02:00 And the other thing too is that no matter where I use my budget
- 02:03 if I sometime decide that I need to rename my table or point to a different column.
- 02:07 I can update the budget measure once and it will flow through every measure that
- 02:11 cascades off of that, so that's kind of nice.
- 02:14 Now, we can also go and do things like subtracting one measure from another.
- 02:18 A classic example of this is a variance where we take a budget and
- 02:21 we subtract the actual measure.
- 02:23 Nice and simple, really easy math there, just subtract one from the other.
- 02:28 Now, you think okay, well, subtraction, adding, pretty easy.
- 02:32 Multiplication, use an asterisk, no problem.
- 02:35 Division, you just use a slash to divide it.
- 02:37 Well yes, but Power BI doesn't really handle divide by zero errors very well.
- 02:44 So while you can say budget divided by something, it's not a good idea.
- 02:48 Because if you trigger an error, a zero somewhere,
- 02:50 that will trigger an error that you're not gonna like.
- 02:52 So instead, we have a specific function.
- 02:55 Now, I'll show you this one here.
- 02:56 This is for Pre-Tax Sales, and what we use is we say that the measure,
- 03:00 the formula is gonna be = DIVIDE.
- 03:02 We put the numerator, so in this case we would divide the revenue by.
- 03:06 The next part is what's the denominator, which will be 1 + the Tax Rate.
- 03:10 Let's say the tax rate is 5%,
- 03:11 we'd say I want the revenue divided by 1.05 is what that would work out to.
- 03:16 If it returns a divide by zero error, that's where the third part comes in and
- 03:20 we have a special function for that called BLANK().
- 03:25 That will actually return a blank value instead of a zero.
- 03:28 This is nice because it will now suppress values that are blank and
- 03:31 don't need to be shown.
- 03:33 So this is a particular measure pattern formula that you're gonna wanna remember.
- 03:36 If you're doing division,
- 03:37 we highly recommend you use it doing the divide formula.
- 03:41 Now, we're gonna flip over to Power BI desktop now.
- 03:43 I'm gonna show you quick example of building COMPOUND MEASURES.
- 03:51 So here we are in Power BI Desktop, I'm now going to create a table visual, and
- 03:55 we'll talk more about the table visual later.
- 03:59 But I'm going to grab the Categories and put that down on the left-hand side, and
- 04:04 I'm going to throw my Sales on this particular visual so we can see that we've
- 04:07 got a nice little table with some sales numbers by category, that's good.
- 04:11 I'm also going to throw my Budgets on here as well.
- 04:15 Now, this is kind of an interesting thing but what I'd like to do is I'd
- 04:18 like to actually take this and figure out what the variance is between the two.
- 04:22 So, I'm gonna do that by saying well, let's right-click on the Sales table and
- 04:26 we'll see New measure.
- 04:28 And in this case here we are gonna call this one Variance $.
- 04:32 And we're gonna say that this is equal to the Budget $ minus.
- 04:38 And notice this IntelliSense comes up as soon as I type the square bracket,
- 04:41 the Sales $, and we'll hit Enter.
- 04:45 At this point, I can now grab my Variance $ and throw it on my table.
- 04:50 I notice that I did a lousy job of my formatting on this, so
- 04:54 I'm gonna go back to my Variance measure.
- 04:57 Onto the Modeling tab and I'm gonna change this to use zero decimal places,
- 05:04 and there we go.
- 05:04 I've got a nice little piece here that's going through and it says hey,
- 05:07 you know what, my variances are showing here.
- 05:10 If I don't like the way the sign is going on this I could always flip the sign
- 05:14 around in the other direction.
- 05:15 So that's an easy thing to do in this particular case.
- 05:19 Maybe I wanna come up with a sales target for my budgets here.
- 05:23 What I could do is I could again come back and say, let's go right-click on Budgets,
- 05:29 we'll say New measure, and I'm gonna call this one Target Sales,
- 05:34 and we'll call this one equal to Budget times 1.05.
- 05:38 So 5% of our budget and that should be good.
- 05:41 Again, I'm gonna go and appoint to good form of the part activate this.
- 05:45 We'll say, zero and we'll put our Target Sales on the same visual as well.
- 05:52 So once again, you can see that the Target Sales are now showing up and
- 05:55 they are indeed higher than Budget.
- 05:57 So this is how we build our compound measures.
- 05:59 It's not extremely complicated,
- 06:01 it's just that we can actually reuse these things more and more.
- 06:04 The big question you might have, which column do I put them on or
- 06:06 which table do I put them on?
- 06:08 It doesn't really matter because the underlining measures are fully qualified.
- 06:12 That really comes down to which table do you wanna store them on?
- 06:14 If you don't like where it is, move them somewhere else.
- 06:16 And that's pretty much it.
Lesson notes are only available for subscribers.