Quick measures and then explain how you can edit them and then to give you an idea of the range of different things quick measures can do we'll see examples to do with dates how to create running.
Totals and also how to create quick measures using text so let's get started to get started i've imported some data from a simplified version of the movies.
Workbook we've used in several previous videos in this series i'll drop a link in the video description so you can download and use this file yourself and once you've.
Imported all the data from it you'll end up with two tables one called movies containing all the data and an almost empty table called all measures which we'll add our measures too shortly.
The first thing i'm going to do in the report is create a table visual which is going to show a list of the film genres alongside the average runtime so i'll add those two fields to the table and.
So in the format pane let's head to the value section and let's increase the font size up to say 13 that should be good enough next i'd like to create two measures one.
That shows the average run time of films which didn't win any oscars and another to show the average runtime of films which did win oscars now these aren't particularly difficult.
Measures to create but if you're just not sure where to get started or you just can't be bothered writing them out yourself you can create quick measures to do this sort of thing for you.
Let's right click on the all measures table and choose to create a new quick measure in the dialog box that appears you can then choose the calculation you want to.
Base your measure on i'm going to select the filtered value option then depending on the value you've selected from the calculation drop-down list you'll have to fill in a variety of.
Other boxes
So here we're going to add the base value which is going to be the runtime field so from the movies table on the right hand side we can drag the runtime.Field into the base value box it defaults to a sum of runtime again but we can change that to an average fairly easily then we can add another field that we.
Can use to apply filters so this time we'll go with the oscar wins field and then once we add that in we get another drop down list populated with.
The values of that column so i can click on the drop down arrow there i'm going to create this first measure to show the average runtime of films with zero oscar wins.
You can select multiple options here by holding down the control key and clicking on other boxes but i just want the one for zero once we've done that we can click ok and.
Our new quick measure will be added to the all measures table at this point we can delete the delete this column column so let's right click on that and.
Choose to delete it from the model and that'll convert our table into a proper measures table so we can see the symbol changes there at this point you can use your quick.
Measure just like any other measure you've created let's drag it into the table visual so we can see the results it produces first then you'll probably spot a few basic.
Things you might like to change for instance the formatting to display decimal places and maybe the measure name as well to make it more readable to do that just like with any other measure.
You can select it in the fields list and
Then we can change the number of decimal places let's go for two if you want to change the name of the measure then you can edit the code.That's been generated for us let's just zoom in to make that's a little easier to read so i'll change the measure name so it's called something like average runtime.
Losers so it'll be a little bit more insulting with our measure names if there's anything you want to change about the way the code has been laid out to make it a bit more readable then you.
Can do that too this is not a particularly complicated measure we may have done something similar to this in previous videos in the series so you'll probably recognize.
The average function maybe the calculate function and this basic filter that's been applied here so where the oscar wins is in this list of numbers i'm just going to change the layout of.
My code to make it suit my preferences so i'll put calculate on its own line the average function on its own separate line and then the filter on its own line and finally close that set of round.
Brackets on a separate line i don't need to change this but i think this part the filter itself is a little more complicated than it needs to be the reason the in operator is used is in.
Case we selected more than one value but as we've only chosen one we can simplify this by saying equals zero rather than in zero so having done all that we can.
Confirm our changes and we'll see that those changes have been reflected with the column name and the formatting of the measure um and it hasn't changed the results of.
Course the filter still works in the same way we've just simplified the code a little bit you can use the code generated by a quick measure to quickly create other.
Similar measures let's use our average runtime losers measure to quickly generate one for the average runtime of winners if we select the measure we can then.
Copy all the code from the formula bar and then we can create another new measure so this is a standard measure this time rather than a quick measure and then paste all this code in.
We can modify the name so it's called average runtime winners and then change our filter so it finds all the films with an oscar win of greater than zero or greater than or equal to one if you.
Prefer having done that we can just update that new measure and then we can add that in to our table visual.
And we'll see that we get a similar result with a different filter using our quick measure as the base you can refer to an existing quick measure in new quick measures that you.
Create for the next example i want to compare the two measures we've already created by subtracting the value for losers from the value for winners that's a fairly simple measure to create.
And write out ourselves but there's also a quick measure option to create it for us as well so let's right click the all measures table and choose to create a new quick measure.
For the calculation this time we're going to scroll down to the mathematical operations section and choose the subtraction option so what we have to do here is refer to.
Two fields now we can refer to fields in the movies table but we can also refer to measures that we've created so i'm going to go with the average runtime winners in the base value and.
Then subtract the average runtime losers we can then click ok and now a new measure will be created just to show you the results if i select it first that's the basic calculation we.
Could easily have written that out to ourselves maybe the title is a little long-winded um let's maybe change this to say average runtime winners versus losers.
Make it a little easier to read then we can simply add that into our table visual and there's our end result sometimes a quick measure will need a.
Little more work to get exactly the results you want so you can see here some of our film genres didn't contain any oscar winning films so they have a blank for the.
Average runtime winners that blank has been treated as a zero for the new measure we've created so we end up with some results which don't really make much sense.
I prefer to replace these with blanks so let's head back to the measure we've just created and we can modify the code in the formula bar let's add an if function.
And then i want to check if the average runtime winner's measure is blank you'll remember how to do this if you've watched previous videos in the series so let's just do this fairly quickly.
And then if that's true we'll produce a blank in the result otherwise we'll produce the result of the subtraction that we've already created.
So that's the entire measure now created so if is blank average runtime winners produce a blank otherwise subtract average runtime losers from average from time winners.
Now we can update that measure and we should see that those incorrect values get replaced with blanks in the final result the quick measures we've used so far.