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.
Performing Math with Compound Measures.xlsx871.3 KB Performing Math with Compound Measures - Completed.xlsx
873.5 KB
Quick reference
Performing Math with Compound Measures
An overview of building measures based on existing measures.
When to use
When you want to create a new measure that refers to other measure(s) that are already defined in your data model.
Instructions
The general process is:
- Create a new measure
- Choose the table on which to store the measure
- Name the measure
- Write the measure in the Formula field
- Click the Check Measure button to confirm there are no errors
- Set formatting options as desired
Specific to Compound Measures
- Never fully qualify a measure (no need as their names must be unique)
- You can add, subtract, multiply or divide measures against each other or numeric constants
Examples
- Bonus Range = [Budget] * 1.05
- Variance = [Actual] - [Budget]
- % Variance = DIVIDE([Variance],[Budget])
Hints & tips
- Measure names must be unique
- Use auto-fill, arrows, and the TAB key as much as possible when writing your measure to avoid syntax errors
- Shortcut to a list of existing measures by typing [ as this will pull up a list of measures only
- You do not preface a compound measure with the table name because measure names must be unique
- It is a good idea to wrap division calculations in the DIVIDE function to avoid errors
- 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.