Basic Measures – Application

Quick reference

• 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.