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. You can download the source data files for the course from the resources section of your Lessons page.
Basic Measures – Application.pbix418.7 KB Basic Measures – Application - Completed.pbix
431.2 KB
Quick reference
Basic Measures – Application
Creating measures for our sample model.
When to use
You want to practice creating basic measure patterns.
Instructions
Creating basic measures:
- Right click the table you want to hold the measure -> Add measure
- Enter the formula for the measure
- Set the default formatting options
Sample measures
- Budgets $ = SUM(Budgets[Amount]
- Sales $ = SUM(Sales[Amount])
- Sales (Units) = COUNT(Sales[Units])
- Last Sale = LASTDATE(Calendar[Date])
Applying measures
- Replace all numeric columns in the visual with the appropriate measure
Modifying Measures
Last Sale is not correct, it’s giving the last date in the calendar, not the last sale
- Select the measure
- Change the formula to LASTDATE(Sales[Date])
- Change the Home table to Sales
Units should be a sum, not a count
- Select the measure and change the formula to =SUM(Sales[Units])
Hints & tips
- By right clicking the table in the fields list and choosing Add Measure, the measure is automatically added to the correct table
- Updating a formula in the measure updates all visuals immediately
Login to download
- 00:05 So let's build some Explicit measures.
- 00:08 We're gonna start with a couple of simple ones.
- 00:10 Instead of using the Implicit measure for Total Sale where we just drag in audio
- 00:14 visual, I'm gonna create a new one that I define.
- 00:17 So we're gonna right click and we're gonna say New measure.
- 00:21 This will pop up a little formula bar here where I can say my measure is going to be
- 00:25 called sales dollars and it is going to be equal too sum of open parentheses.
- 00:31 You will notice that the IntelliSense pops up here.
- 00:34 So I can start typing the first letter of my table name here S and
- 00:37 you say, there's sales.
- 00:39 I can arrow down until I get to total sale, tab, and this will
- 00:43 create me a nice fully qualified reference with a table name and the column name.
- 00:47 I can now close my parenthesis and hit enter, and
- 00:51 I now have on my table, a new measure showing up.
- 00:54 Now it shows up with a calculator icon.
- 00:56 That tells me that this is one that I have explicitly defined.
- 00:59 It's not something that's just using a regular hey this is a numeric field.
- 01:03 So I know this is the one that I built or somebody built it at any rate,
- 01:06 which is kind of nice.
- 01:08 Now what if I want to change the formatting for it?
- 01:10 Well, I'm going to select the measure.
- 01:12 I'm going to go to the modeling tab.
- 01:14 And at this point you can see that the formula's still here.
- 01:17 I can now get in here to play around with the different components.
- 01:20 So I could say, let's put in a zero for the number of decimal places.
- 01:24 So that looks kinda cool.
- 01:25 I can change dollar signs and thousands and all that kinda stuff.
- 01:28 Great.
- 01:30 I'm gonna create a couple more measures here, as well, while I'm here.
- 01:33 Let's create a new measure.
- 01:35 Right click, New Measure.
- 01:37 This one we're gonna create for sales in units.
- 01:42 And this one is going to be equal to the count of the sales table.
- 01:48 And the units sold column.
- 01:50 Close parenthesis and hit enter.
- 01:53 Now, you'll notice that at this point this piece greys out.
- 01:55 If I click in the formula again I can get back.
- 01:58 I can set this and say, hey that looks cool.
- 02:00 Good stuff.
- 02:01 I have now got sale's units.
- 02:03 So that looks great.
- 02:05 What else could I create to this one?
- 02:06 You know what? How about this?
- 02:07 Let's go to the budgets table, we'll right click, let say New Measure,
- 02:12 and we'll call this one budget dollars.
- 02:17 This is gonna be equal to the sum of open parenthesis.
- 02:21 And you'll see that I can actually arrow down just once to budget amount Tab,
- 02:26 close my parenthesis and hit Enter.
- 02:29 I'm gonna click in the formula bar and set this guy here to 0 and
- 02:33 Enter, I'm good to go.
- 02:35 I'm gonna create one more measure.
- 02:37 This one's gonna be a little bit special.
- 02:39 We're gonna go to the Calendar table and I'm gonna use a formula I haven't
- 02:41 told you about quite yet, which is called Last Date.
- 02:46 So we're gonna go New Measure.
- 02:49 This one here I'm gonna call Last Sale.
- 02:52 And this one is going to be LastDate and this returns the last date.
- 02:57 It's kind of like Max but specific for dates.
- 03:00 Max would actually work to be honest with you.
- 03:02 And we're gonna go with Calendar.
- 03:05 Arrow down here we are, to date.
- 03:08 Close the parentheses and hit Enter.
- 03:11 Now because this is a date I want to format this a little different.
- 03:14 I'm not really cool with the date and time, so
- 03:16 I'm going to click my little drop down arrow.
- 03:18 I'm going to go to Date Time.
- 03:19 I'm going to choose this format here for March 14, 2001.
- 03:23 So I've now created the four measures that I actually want to use.
- 03:26 Now I need to swap them out of my tables.
- 03:29 So I'm gonna grab this first one here.
- 03:32 Now I have my amount which is my budget amount fully name because nobody renamed
- 03:36 it but it's still an implicit measure.
- 03:38 So let's grab Budget and put it on to this chart.
- 03:42 And you'll notice, that when I mouse over these two items here, Canned Beer, for
- 03:47 example.
- 03:47 You'll notice that the values are exactly the same.
- 03:50 So our measure is working quite nicely, which means we don't need amount anymore.
- 03:55 I'm also going to pull on our Sales dollars.
- 03:58 I'm gonna actually gonna put it above Budget,
- 04:00 because I think Sales should come first.
- 04:02 And again, you'll notice that, Draft Beer, 1.4 million and our draft beer 1.4
- 04:06 million these are also exactly the same so I can now get rid of total sale.
- 04:12 So what's the difference between implicit and explicit?
- 04:15 Well, here's the difference.
- 04:16 If I right click, and I can see that I can see data, this is an explicit measure.
- 04:21 It only gives me the ability to see data which essentially tells me the tops of
- 04:25 each of the bars.
- 04:26 So not really a lot of data exposure at all.
- 04:29 The difference is though, when I look at one of these other guys over here,
- 04:33 this one's all using implicit route measures, you also see See Records.
- 04:36 And See Records shows us the entire record set that makes up the data set.
- 04:40 So this is a little bit too much information for
- 04:42 me to expose as in my opinion.
- 04:44 So, I don't wanna do that.
- 04:45 So, what I'm gonna do is again, I'm gonna grab this chart here and
- 04:49 instead of Total sale and Budget, I'm gonna put on sales and
- 04:53 I'm gonna put on my budget here as well.
- 04:56 I've now got a nice chart that looks like it works quite well.
- 05:00 I'm gonna go back over now to page one and
- 05:02 I'm gonna make the changes to the charts in here as well.
- 05:05 So we'll grab the total sales by category here.
- 05:07 This doesn't have any budget information so
- 05:09 we can collapse this down instead of total sale.
- 05:12 Again, I'm gonna put on sales dollars, get rid of the total sale
- 05:17 on this particular chart here where sales and location names.
- 05:20 I'm gonna go and put it on, again, my Sales dollars.
- 05:22 Get rid of Total sale.
- 05:24 I'm not gonna go and replace this measure here with the latest date.
- 05:28 I'm gonna pull on that cool measure that I made for my last sale.
- 05:31 And we'll put that right in here.
- 05:34 And notice that we go from December 3rd to December 31st, so that's not good.
- 05:37 And the reason being this because we pulled the last sale date off of
- 05:41 the calendar which has a calendar that runs all the way to December 31st.
- 05:45 The last sale really is what's the last sales transaction in the sales tables.
- 05:49 So this formula's not right.
- 05:51 So we need to fix that.
- 05:52 So we'll click on last sale, it brings up this particular piece and
- 05:55 we realize that hey, last date calendar date key, that's not the right one.
- 05:59 We want last date from the sales date column.
- 06:03 So when you hit enter, and you'll notice that it changes back to December 3rd,
- 06:07 as it should.
- 06:08 But the Measure still lives on the Calendar table.
- 06:10 So if that happens to you, on your Modeling tab you can change this to say,
- 06:15 go back to where you belong.
- 06:16 And there we are, that's perfect.
- 06:19 My total sales are based off of Sales, so I'll swap that in here.
- 06:25 And my unit's sold.
- 06:26 Let's go and put Sales Units in here as well.
- 06:30 39,000, that's not right.
- 06:33 So if I again look at the formula here I realize,
- 06:36 I don't want to count the number of units sold, I want to sum the units sold.
- 06:41 The count of a bunch of records that have ten in them is not the same, obviously,
- 06:45 as a sum of a bunch of records that have ten in them.
- 06:47 So now when I click in my canvas, and that formula commits, everything is good.
- 06:51 I also have the ability to force people to use my measures
- 06:55 instead of the numeric columns, and it's super easy.
- 06:58 Right-click hide now to have to use my budget measure.
- 07:04 I can also do the same here with the total sale, right click and
- 07:07 Hide, right click on Units sold.
- 07:10 Now I don't have anything for units price so I'll leave that one alone right now.
- 07:14 But you get the idea we can actually build these guys down and
- 07:17 store and only present the formulas that we want our users to actually use.
Lesson notes are only available for subscribers.