Performing Math with Compound Measures

Quick reference

• 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.