Basic Measures – Application

Quick reference

• 00:05 All right, so let's look at building our own explicit measures.
• 00:09 One of the things I wanna call out really quickly about this pivot table, is that
• 00:13 you can see that we've got sales dollars and budget dollars on the pivot table.
• 00:17 And yet, if we look at the field's list,
• 00:19 the fields that it's actually using is Budgets amount and Transactions Amount.
• 00:24 So it's really difficult.
• 00:25 Because we've actually used an implicit measure and dragged this on and
• 00:29 renamed them, it's very difficult to actually read these in the field list
• 00:32 because we can't actually marry them back.
• 00:35 It doesn't change them in the field list where it does change it in
• 00:37 the values area.
• 00:39 Now this is something you're going to see is very different when you start using
• 00:41 an explicit measure.
• 00:42 So let's rip these guys off and we'll build our own.
• 00:45 And here we go, we'll remove sales and you'll notice it unchecks it for me, so
• 00:48 that was definitely a field we were looking at.
• 00:52 To create a new measure, an explicit measure,
• 00:55 we're gonna go to the Power Pivot tab.
• 00:58 Now in Excel 2013 on the Power Pivot tab, these will be called calculated fields.
• 01:02 They're not the same as a calculated field from a regular pivot table.
• 01:05 You want the ones from the Power Pivot tab.
• 01:08 In Excel 2016 and higher, we've got measures,
• 01:11 which is what they should be called.
• 01:13 So we're gonna click on Measures.
• 01:14 We're gonna go New Measure or click on Calculated Fields, and
• 01:17 say New Calculated Field.
• 01:20 The first thing that it will offer you is the opportunity to
• 01:23 choose which table you would like to store your measure on.
• 01:26 I like to store my measure on the fields that are closest to what I'm gonna be
• 01:29 working with.
• 01:30 So because I'm going to start by summing the Transactions Amount column,
• 01:34 it makes sense to have this live on the Transactions table.
• 01:37 I'm gonna go give this measure a name.
• 01:40 I'm going to call it something like Actual, that's a good name for a measure.
• 01:44 And then I'm gonna hit Tab, which takes me in the description field.
• 01:46 And if your like every excel pro on the planet,
• 01:49 you fill out this documentation by pressing the Tab key.
• 01:52 That's gonna take you to the fx button, which would let you browse some formulas.
• 01:56 Then to the Check Formula button, to check the formula that you haven't written yet.
• 02:00 And finally Tab it one more time and you get into the formula field.
• 02:04 Now the formula that we're gonna work with is,
• 02:06 we're going to take the Transactions Amount column and we're gonna sum it.
• 02:09 So, we'll start with SUM and then I'm going to press Tab.
• 02:14 Now, the reason that I do this is because it autocompletes the formula for me.
• 02:20 It'll even change the syntax or the highlighting or the casing for me.
• 02:24 All of the stuff gets fixed for
• 02:25 me right away when I actually commit something from the drop-down list.
• 02:29 At this point, what I'm gonna do is I'm gonna type T for transactions.
• 02:32 That's the name of the table, of course.
• 02:34 And you'll notice that it highlights Transactions Amount for me, so
• 02:38 I can press tab to autocomplete that.
• 02:40 The only thing I need to do is close the parenthesis.
• 02:44 I really recommend you try and commit every piece of a formula you can using
• 02:48 the Tab key because that way you'll make sure that you get the correct syntax for
• 02:52 it, which is excellent.
• 02:55 Now, the next thing you should do is you should
• 02:58 always click the Check Formula button.
• 03:00 This will actually evaluate the formula to see whether or not it's working correctly.
• 03:05 Now, one thing you wanna remember here is that just because you get the green
• 03:08 checkmark doesn't mean it's gonna work.
• 03:10 My accounting teacher used to sell me just because it balances,
• 03:12 doesn't mean it's right.
• 03:14 But what we can guarantee is that if you don't get the green checkmark,
• 03:17 it won't work.
• 03:18 So you wanna make sure you have that.
• 03:21 A cool thing about the Power Pivot formulas, down at the bottom here,
• 03:24 I can choose my default number format.
• 03:26 So I'm gonna choose Number with a thousand separator and
• 03:29 I'm gonna drop the decimals off.
• 03:31 And now, what you can see is with my green checkmark and
• 03:34 my nice formula, I'm gonna say OK.
• 03:37 And at this point, it'll add it to the pivot table for me and
• 03:40 you can see that everything is working nicely.
• 03:43 But better than that, check this out.
• 03:45 It added a new field for me called Actual.
• 03:47 You can see that it has got a little fx button in Excel 2016 to let me know
• 03:51 that it's an actual measure.
• 03:53 And it's called Actual in the table.
• 03:55 It isn't called Sum of Actual or anything else.
• 03:57 It's giving me the name that I chose for it before.
• 04:00 So now it's really easy to identify, which is great.
• 04:04 I'm gonna go back now and I'm gonna modify this.
• 04:06 Cuz one thing I wanna do is I wanna flip the sign on this guy.
• 04:09 So I'm gonna go back to Measures, Manage Measures.
• 04:13 And this will bring me up a list of all the different measures I have,
• 04:17 which right now is only one.
• 04:18 I'll click Select it, Click Edit, and I'm gonna multiply this,
• 04:23 we'll go right to the end, times minus 1.
• 04:26 Check the formula, everything's good, and we'll say OK and close.
• 04:32 And you can see that it flips the signs.
• 04:34 So now my revenues are positive, my expenses are negative, and
• 04:37 my grand total is negative.
• 04:38 Which is telling me that there is probably a problem going on here.
• 04:42 So that is my revenue, my actual, now let's do budget.
• 04:49 Measures, New Measure,
• 04:51 this one I'll place on the Budgets table because that's where the budget amount is.
• 04:57 And I think I'm even gonna go and call this one just budget.
• 05:01 I'm gonna come down and write a formula and what I'm gonna try right now
• 05:05 is what somebody might try without is I'm gonna go with Budgets Amount.
• 05:09 And we'll just go with that.
• 05:10 And what you'll see here is when you click Check Formula, it comes back and
• 05:14 tells me there's a calculation error.
• 05:15 The most important part here is that we tried to aggregate or
• 05:19 we tried to create a measure which contains many values.
• 05:24 But does not specify an aggregation function such as min max sum or count or
• 05:28 something like that to get a single result.
• 05:30 So when you see this, something about the fact that you've lacked and
• 05:33 aggregation function.
• 05:34 What it means is you forgot to do something like this,
• 05:38 putting your sum around it.
• 05:40 So that's a message that comes up from time to time.
• 05:43 When you click Check Formula, it'll fix that.
• 05:45 So remember, if you're referring to that raw column,
• 05:47 you can't just refer to the column.
• 05:49 You need to actually aggregate it first.
• 05:51 I'll multiply this by minus 1 to be consistent with my transactions or
• 05:55 my actual.
• 05:56 We'll check, everything's still good.
• 05:59 Set my number format, roll down my decimals,
• 06:02 click my thousands separator, and say OK.
• 06:04 And now what you'll see is my budgets also get added to the table.
• 06:09 My revenue budgets are positive.
• 06:12 My expense budgets are negative.
• 06:14 And at the end of the day,
• 06:15 the bottom line looks like it's actually turning correctly as well.
• 06:19 It looks like May we've actually got some forecast and actual revenues for
• 06:22 positive things.
• 06:23 But March and April we've actually said, hey look,
• 06:26 we expect that we're gonna be losing money in this particular case.
• 06:29 So there's a couple of measures that are working quite nicely.
• 06:32 You can see that they get the little fx symbol here in Excel 2016.
• 06:36 And we've actually got a named field that is consistent in the values area,
• 06:40 which is great.
• 06:40 We've defined a default number format.
• 06:43 Every pivot table we add it to it will use our exact same number format,
• 06:47 and that is just a fantastic thing.
• 06:49 So that is some of the first benefits of working with an explicit measure.

Lesson notes are only available for subscribers.