Performing Math with Compound Measures

Quick reference

• 00:04 So now that we know how measures calculate and we've built a couple, I wanna actually
• 00:09 amp this up a little bit and I wanna come up with a compound measure.
• 00:12 The next natural step in building a financial statement like this,
• 00:16 is to generate a variance,
• 00:18 the difference between actual and budget to see how well we're doing.
• 00:22 Now, to do that we obviously have to subtract one measure from another.
• 00:26 So let's go do it.
• 00:28 We're gonna go to Power Pivot tab again, and we're gonna choose Measures, or
• 00:33 Calculate Fields, New Measure, or New Calculated Field if you're in Excel 2013.
• 00:38 Now the first issue that we have is where should we put our measure?
• 00:43 This one here is going to be a variance
• 00:45 measure which is going to subtract my actual from my budget.
• 00:49 So if I'm doing that, I could store it on either the transactions table or
• 00:54 the budgets table.
• 00:55 Which one should I choose?
• 00:57 And the answer is it really doesn't matter.
• 00:59 The reason being is because, we actually already have the actual on the budget
• 01:03 defined and within those two measures, we've already said it's the sum
• 01:08 of the transactions amount and the sum of the budgets amount.
• 01:12 They're fully qualified with the table name, so the compound measure can
• 01:16 live anywhere because we know what the definition of these two original guys is.
• 01:21 So I'm gonna go ahead and say, let's put it on the transactions table.
• 01:24 I prefer to store most of my measures in the same place if I can.
• 01:27 So if it touches one, I'm generally gonna put it on the transactions table here.
• 01:31 I'm gonna give my measure a name, I'm gonna call this one Variance $.
• 01:36 And now I'm gonna go and
• 01:38 I'm gonna do something different here than I did beforehand.
• 01:41 Last time we actually built the measure we had to aggregate the column,
• 01:45 we had to say sum of transactions amount.
• 01:47 We prefaced the name of the column with the table name, and
• 01:51 you can actually see why here really easy.
• 01:54 We have an amount column on the transactions table, and
• 01:57 an amount column from the budgets table.
• 01:58 So, it's important to say sum of transactions amount because we wanted
• 02:02 to ensure that it was always pointing towards this table and
• 02:05 not the amount column in a different table.
• 02:08 When you're working with compound measures though,
• 02:10 when you're referring to already existing measure.
• 02:13 You never preface it with a table name, and the reason being is because
• 02:18 unlike my amount columns there, a measure name must be unique.
• 02:22 So you can never have a collision where you've picked up the wrong one.
• 02:26 So to refer to a measure, we don't bother with a table name,
• 02:29 we just start with a square bracket.
• 02:31 And you'll notice that it comes up right away with all of the measures that I've
• 02:35 ever created, actual budget, sum of amount and sum of amount two,
• 02:38 which were the original implicit measures that I created.
• 02:41 I really wish I could I make those go away.
• 02:43 Regardless, in this case, I'm gonna type tab because actual is selected, and
• 02:48 them I'm gonna say minus, square bracket, and then budget.
• 02:52 And tabs, so I could put my downer or once in tab.
• 02:55 And I tried to make this is quick as I can to type it without making any
• 02:59 mistakes or not.
• 03:00 So the more I can use my arrows and
• 03:01 tab to auto complete to better off things will be.
• 03:04 Of course, you never leave this window here without clicking Check Formula,
• 03:08 we've got no errors, that looks good.
• 03:10 So now I'll set my default number formats.
• 03:12 And I'm gonna set them consistent with what I had before.
• 03:16 We'll say OK, and you're gonna see that we now get a new variance column added and
• 03:21 it is my revenues are lower than budgets, so therefore that's bad, it's negative.
• 03:26 And why expenses are lower than my budget, they're less negative so
• 03:30 that's a positive number, which is good because I am happy about that,
• 03:33 my expenses are actually below what the actual budget was.
• 03:36 So everything there looks pretty good.
• 03:39 Could I go and layer on even more compound with this?
• 03:42 Well, yeah if I wanted to actually get a variance percentage, I can do that too.
• 03:48 I can go back to Measures, New Measure, I'll store this again
• 03:53 on the transactions table, and I'll call this variants %.
• 03:58 And this one is gonna be equal to
• 04:02 the variants $ divided by my Budget.
• 04:07 Now one thing, though, this can cause some issues.
• 04:11 So it's a little bit better, there's another function that we can use it's
• 04:16 actually what we refer to generally as the safe divide function.
• 04:19 It's actually a divide function, so div tab, and this says what's the numerator?
• 04:25 Now we'll say variants and then comma, for your denominator and then our budget.
• 04:31 The nice thing about this particular function here is that it guards against
• 04:35 divide by zero errors that can cause some problems.
• 04:37 So when we work with this one now here we'll say check formula,
• 04:41 I can now say let's go with the number but let's change this guy to a percentage.
• 04:47 And we'll put one decimal place on that percentage, and if there happens to be
• 04:50 thousandths of percents, we'll give the thousandths separators as well.
• 04:54 And now you can see that I get a nice little error, or a nice little variance
• 04:58 here, that shows up all the way down the table telling me what's actually going on.
• 05:02 What's up or down in the grand scheme of things, so
• 05:04 this actually works fairly well for me as well.
• 05:07 I'm pretty happy with the way it works, although I might change things around
• 05:11 a little bit and move the the math around if I'm not happy with dividing by budget.
• 05:15 Maybe I want to divide it by actual or something instead,
• 05:18 but the key thing that's available to us here,
• 05:20 is that we have a nice way to actually go and write our formulas.
• 05:24 And you can see that we're creating measures that we're laying on top
• 05:27 of each other.
• 05:28 And if anything ever goes wrong with actual I can fix it once, and it flows
• 05:32 through all of the rest of the measures that are actually in this package.

Lesson notes are only available for subscribers.