3: Building a Data Model to support self-service reporting
3.12 Introduce quick measures
3: Building a Data Model to support self-service reporting
3.12 Introduce quick measures - Video Tutorials & Practice Problems
Video duration:
7m
Play a video:
<v Instructor>Okay, so in this sub lesson</v> we're gonna describe the benefits of quick measures. We'll introduce the five quick measure calculation types and their calculations. We'll go ahead and create a quick measure and then we'll discuss some limitations and considerations. So let's go ahead and get our supporting Power BI desktop file for this sub lesson opened up. So I've got my GitHub files open here. And so I'm gonna go to L03_13, double click on it to open it up and give it one moment. Okay, so the desktop file's open here. Let's first discuss some of the benefits of quick measures. So quick measures are, as advertised, a nice easy way to create measures without having to understand docs yourself. So you can go through a few configuration screens and actually have a wizard build a docs expression for you. Now, this is a nice way to dip your toe into the water when it comes to docs because you can go in and get them configured have a docs expression we built for you and then take a look at how the expression was actually built up. And I find this as a really good way to learn any language. We kind of showed you that back in the M module when we were back in the power query editor on how you can actually learn M by going through the configuration screens and watching the language that's getting developed for you. So it's just a great way to kind of learn by watching. So those are some of the benefits of quick measures. You can get in to start building docs expressions and fairly complex ones without actually having to do any work on your own. Okay, so let's go ahead. And we're gonna take a look at the five quick measure calculation types and their calculations. But first, what I want to do here is I want to set this demonstration up so that we can actually build our quick measure out. So what I'm gonna do here is I'm gonna go to my data tab or my data view, I should say, and I want to open up my order date table because what I need to do here to make this work as earlier on, we had hidden the order date column here. So it was not visible to the port. So what I'm gonna to do here is I'm gonna actually unhide this. I'm gonna go here and uncheck that heightened report view. So the order date is actually now available when we're going ahead and building these measures up. So what I'll do next is I'm gonna go down to my sales table and I'm gonna go ahead and right click on the sales table and say new quick measure. So the third option down here, if you see quick measure we're gonna wait for a dialog box to pop up. And then here's where we can go through and take a look at the five quick measure calculation types. So if I open this up, I can see there's ways to go ahead and figure aggregates per category, filters, some time intelligence functions, totals, mathematical operations, and text. So actually, I guess we have six different quick measure calculation types here. So we will then go ahead and build ourselves out a quick measure here. So I'm gonna go and choose the one for the year over year change. So I'm gonna go ahead and find my year over year change quick measure here. And what I want to do here is it's gonna ask me for a base value. So I'm gonna go to my sales table and I want to choose my total sales measures. I'm gonna take that and drop it in there. And next it's gonna ask me for a date. So I need to go into my order date table and find my order date column, which I just turn back on and drag it into the date here. And we're gonna say number of periods. So this is really the number of periods over which to calculate the change. And we want to do that over one. So I'm gonna go ahead and click okay. And what we'll see here now is that a brand new measurable beget created right here in the sales table called total sales YOY% to stand for year over year percentage. So if I go ahead and make this docs expression a little bit larger here we're gonna see that it actually wrote this out for us. And it's doing some fairly sophisticated things, it's taking advantage of what are called variables which are beyond the scope of this class. But what happens here is we're able to first calculate the previous year sales by taking advantage of the calculate function, using the total sales measure. In this case, we're using the date add docs function has been created for you automatically to go one year back to figure out what the previous year sales were based on, what that context is. So after the previous year sales have been calculated then what we're gonna do is return a division of the total sales minus the previous year sales divided by previous. So here we can actually go ahead and run this. So actually it's already been run so I don't need to do anything but now let's go ahead and use this in a visualization. So I'm gonna go back over to my page number one. I'm just gonna make a little bit more room for some of the data we actually want to bring onto this page. So I'm just gonna move a few things over here. So let's go like that. And maybe we'll go ahead and get rid of quarter day sales on here 'cause we got enough on here. Let's go and grab our total sales. I just need to move the fields pin a little bit. The sales while YOY percentage. So I go ahead and drop this in here and we can see now that we have a calculation that is letting me know how much sales have changed on a year over year basis. So I can take a look at the total sales for this year. So I got this value right here, the 2.7, the total sales from the prior year was 2.5. And that's letting us know that the sales have actually gone up by 10%. We can go down to the next one. We can see the total sales for this month for $2 million. The previous year sales were 2.6. So therefore we've gone down by 22%. So we can go through all the rest of these things here but it was really quick to go ahead and create this measure using the quick measures. And once she saw it was quite sophisticated, so you're able to get a lot of benefit out of this. And as I walked you through in the expression it's a really nice way to actually show you how to build some more complex things out without actually having to do it yourself but learning by having an engine rate at for you. So next, let's take a look at some of the limitations and considerations around quick measures. So I'm just gonna go out to the Microsoft Power BI docs and pull up the document that actually talks about this. Okay, so I'm on the website here now and this URL is inside the GitHub repository. So if you want to go ahead and get it that way you can but some of the few limitations and considerations to keep in mind around quick measures then let's go through the first couple of here, are that you can use a quick measure added to the fields pin with any visual report. You can always see the ducks associate with a quick measure by selecting the measure in the fields list and looking at the formula in the formula bar. Now, one nice thing about this is once the wizard is actually built the doc's expression for you, you're free to go ahead and change that expression, how you see fit. So if you want to modify the name, modify any of the variable names or anything like that you can go ahead and do that because there's no way to get back into the actual creation wizard. So you're now free to do what you want to do with that. Okay, so with that, that brings us to the end of this sub lesson on quick measures.