Locked lesson.
About this lesson
It's now time to apply our knowledge of basic formulas, and build measures that add business intelligence value to our sample model.
Exercise files
Download this lesson’s related exercise files.
Basic Measures – Application.xlsx874 KB Basic Measures – Application - Completed.xlsx
871.3 KB
Quick reference
Basic Measures – Application
A demonstration of creating explicit measures.
When to use
When you want to begin writing basic DAX measure formulae for Power Pivot.
Instructions
Creating basic measures:
- Go to the Power Pivot tab and select
- Excel 2016 & Higher: Measures --> New Measure
- Excel 2013: Calculated Fields --> New Calculated Field
- Select the table on which to store the measure
- Enter the measure name
- Enter the formula for the measure
- Click the Check Formula tab to ensure the measure is working correctly
- Set the default formatting options
Modifying a measure:
- Go to the Power Pivot tab and select
- Excel 2016 & Higher: Measures --> Manages Measure
- Excel 2013: Calculated Fields --> Manage Calculated Fields
- Select the measure to change --> Edit
- Modify the measure as desired
Hints & tips
- When building your measure, it is recommended to use the TAB key to commit each piece of your formula to ensure you get the correct syntax
- When using an aggregation function, ensure you always preface any column name with the table name
- Updating a formula in the measure updates all visuals immediately
- When writing the measure formula, you can make the text bigger by holding down CTRL and rolling the mouse wheel
- 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.