Quick Measures

Quick reference

• 00:05 We're now gonna jump into something kinda cool called Quick Measures.
• 00:09 But before we can do that,
• 00:11 I need to show you how to activate preview features inside Power BI Desktop.
• 00:15 Now, just to be clear on this one,
• 00:18 the reason why I need to show you how to activate preview filters is
• 00:21 because at the time of shooting, this particular feature is in preview.
• 00:26 If it's several months since the course has been released, you may find
• 00:29 that this is actually out in general population and there's no need to do this.
• 00:32 But it's still a good exercise to see what we're gonna do here.
• 00:35 Because you'll be able to see where there's other preview features as this
• 00:38 product is being rapidly developed over time here.
• 00:42 So to find preview features, what we're gonna do is we're gonna
• 00:45 go to the File menu, and we're gonna go to our Options and Settings.
• 00:49 And we're gonna go to Options.
• 00:52 And inside the Options window, you'll notice down on the left-hand side,
• 00:56 there is something called Preview features.
• 00:59 When you find this, you'll notice that right now, I have a bunch of them in here.
• 01:03 The one that I'm focused on is Quick measures, and I'm now gonna go and say OK.
• 01:08 At which point, I'll be told that I have to restart Power BI Desktop.
• 01:13 So that's fine, we'll say OK, and then we'll close and reload Power BI Desktop.
• 01:19 All right, we're back in Power BI Desktop, the quick measures feature has been added.
• 01:23 So what I'm gonna do now, rather than confuse things,
• 01:25 is I'm gonna grab the chart that I have here, hit Ctrl+C to copy it.
• 01:30 I'm gonna go drop it on to a new page by pressing Ctrl+V.
• 01:34 And I think I'm just gonna go and pull the budgets off of this one.
• 01:39 Just to try and get back to our sales number for sales by the end of month.
• 01:43 Because this is a chart that we might actually want to go and
• 01:46 add something to, like maybe some date, time, and totals.
• 01:49 Wouldn't it be kinda neat to have a year-to-date totals
• 01:53 showing on our chart as well.
• 01:55 So, to do that, this is where the quick measures functions actually come in.
• 02:00 So, I can get to quick measures a couple of different ways.
• 02:02 I can right-click on a field in the values area, and say Quick measures.
• 02:06 Or I can pick it up from the field that's here.
• 02:08 Either way, it's the same entry point.
• 02:10 I'm gonna right-click and say Quick measures, and it brings up this dialog.
• 02:13 And it says, all right, select a calculation.
• 02:16 So I'm gonna go and take a look, what looks interesting?
• 02:18 Hey, Time intelligence, Year-to-date total, that looks cool.
• 02:22 So we're gonna grab that one.
• 02:24 Now it asks us, what's the base value that you would like to actually base this on?
• 02:28 And it pulls all of this stuff in from the visual that we've already created.
• 02:31 Are we stuck with this?
• 02:33 No, not at all.
• 02:33 We could x this out and it will say, hey, you've gotta give me a field here.
• 02:37 So I could go to the sales table.
• 02:40 I could grab Sales and drag it in there, or, now sales, by the way,
• 02:44 is an explicit measure cuz it has got the calculator icon.
• 02:47 I don't have to use an explicit measure.
• 02:49 I could grab something else and drag it in and
• 02:51 you'll see that it gives me a sum all of unit price or something like that.
• 02:55 I'm not really a big fan of this because this actually gives us the sum of,
• 02:58 that may not be what I want.
• 02:59 So I would always encourage you to build an explicit measure first so
• 03:02 that you can know what you're actually aggregating.
• 03:06 The second thing is it says, tell me what your date field is.
• 03:09 Well, it's End of Month, no problem, so I'll say OK.
• 03:14 And what it does, it creates me a new measure.
• 03:16 Here's the docs pattern for it and it tells me that it can't display the visual.
• 03:20 And if I click See details, I'm gonna get something that has this beautiful little
• 03:24 thing about an MDX script model error that nobody really understands.
• 03:27 But you'll notice that the text here is the same as the text in red.
• 03:31 And it says the time intelligence quick measure can only be grouped or
• 03:33 filtered by the Power BI-provided date hierarchy.
• 03:36 You go, what the heck is that?
• 03:37 I mean we haven't even talked about hierarchies at all.
• 03:39 Well, here's what's gonna happen.
• 03:41 We're gonna go down and
• 03:42 we're gonna actually delete this new measure that was created for us.
• 03:46 We're gonna get rid of this altogether.
• 03:48 Because that has a specific pattern in it that's key to a field that won't work, and
• 03:53 we're also gonna remove it from our visual.
• 03:55 The challenge is that we tried to build a space on the end of month field, and
• 03:58 that's not gonna work.
• 03:59 We need to replace that with something that Power BI uses,
• 04:02 which is called the date hierarchy.
• 04:04 So we're gonna grab our plain old date field.
• 04:06 Remember, this is the calendar table that has that repeating list of unique dates.
• 04:10 I'm gonna put that in place on end of month.
• 04:13 And we're gonna remove the end of month piece.
• 04:15 Now this gives me the years 2015, 2016 as a hierarchy.
• 04:18 And I'm gonna talk about this more in the next module.
• 04:21 The key part that I want you to recognize now though is that if we go and
• 04:25 right-click and say Quick measures, and we create this measure again,
• 04:29 we'll start a calculation.
• 04:31 We'll go with Year-to-date total, we use Sales.
• 04:34 But this time,
• 04:35 we're using Date as our primary key from our calendar table, and we say OK.
• 04:41 We're gonna see that we get something new added.
• 04:43 It creates the measure for us, that's all there.
• 04:46 You can always edit it if you want to, but
• 04:48 it gives us a visual that shows us our sales and our year-to-date sales.
• 04:52 And of course at the top level of year,
• 04:54 you would expect that they're kind of the same.
• 04:56 Now I'm gonna show you what happens if we actually go and
• 04:59 we expand down a level of the hierarchy.
• 05:01 And we'll talk more about this a little bit later in the next video and
• 05:04 what this means.
• 05:04 But you'll notice that Q1, we have sales of 272,000.
• 05:08 We have sales of 853,000 in Q2.
• 05:11 But if I look up the year-to-date, the sales are 1.1 million.
• 05:14 So the darker bars are giving this year-to-date.
• 05:17 And then, when we move into the next month, you can see that it resets.
• 05:21 If I drill down one more level, you can see that we have our nice little cyclical
• 05:25 set of sales going on here, as well as the ever increasing year-to-date numbers that
• 05:29 reset at the beginning of the year.
• 05:32 Quick measures is a really cool feature that Power BI team is adding new things to
• 05:36 all the time in order to make it easier to build really complex DAX narrow.
• 05:40 So it's well worth checking out but
• 05:42 always review what you get to make sure that's really what you want.

Lesson notes are only available for subscribers.