We'll use that technique to create a filtered list of categories and show you how to exclude categories from a list so let's get started to get started i've created a new report.
And i've already imported the data from our standard excel movies workbook as usual i'll drop a link in the video description so you can download this file yourself and follow along with the.
Video if you'd like to all i've done since importing the data is tidied up the data model a little bit and i've made sure to hide all of the id columns just to keep the fields list a.
Little bit clean and tidy when we build our visuals back in the report view to get started in this report i'm going to add a table to the report page and in that table i'm going to display the.
Then i'm going to create a new measure called average runtime or avg runtime for short and we're simply going to use the average function and look for the.
Runtime minutes field in the film table having done that and we've created the measure we can delete the delete me column from the all measures table just to convert this into a proper measures.
Table and then when i've confirmed that i want to do that we can add that measure into the table visual on the report page.
Just to make sure we can see everything more clearly i'm going to hide the filters panel and then in the format pane i'm going to increase the font size of the values of.
That table up to about 13 and that should be a little bit easier to read so far in this section of the tutorial we've looked at how to apply new filters in a measure using a variety of.
Posts Related:
- What is Make
- Power Automate Advanced Tutorial Office
- VBA to Upload single or multiple files to Google Drive Office
Functions and in the previous video we - ID Card Make
Looked at how to remove filters in a measure in this video i'd like to look at applying filters in a measure using a.Column that's already being used to apply a filter so in this table visual you can see that the genre column is being used to create a filter context for each row which affects the results.
Returned by that average runtime measure i'd like to create a new measure that calculates the average runtime but just for a single specific genre i'm going to pick science fiction for this first.
Example so to do that i'm going to right click the all measures table and choose to create a new measure and i'm going to call this one avg runtime.
Sci-fi we'll make this one equal to the result of the calculate function the expression i'd like to calculate is the average runtime so i could create my.
Average function again but as i already have an average runtime measure let's just reference it for convenience then the filter that i'd like to apply i'm going to reference the genre field.
In the genre table and check where the value of that is equal to science fiction at that point i can close around brackets and hit enter.
And then i can add that new measure to my existing table to see the end result shows me for every single genre it shows the average run time for science fiction films.
So this tells us that the filter argument we've just created destroys the original filter context and replaces it completely with the one that we've just written.
This sort of thing can be quite useful
When you want to compare one specific category with all of the other categories in the same column so to do that let's create a new measure.I'm going to call this one avg runtime versus sci-fi and then all we're going to do is subtract one measure from the other so we'll say.
Avg runtime minus avg runtime sci-fi when we've created that we can add that to the table visual and then we can see this uh this set of values as a column.
Of numbers we can see which genres on average have a longer or shorter runtime than science fiction this sort of thing looks a little bit.
Better displayed either as a chart or maybe even as just data bars in the table so let's just increase the column width there so we can see this a little more.
Clearly and then i'm going to right click on the average runtime versus sci-fi field in the values bucket in the field well choose conditional formatting and then.
Choose data bars i'd like my negative bars to be displayed in an obviously different color i'm going to pick orange for this example.
And then click ok to see the end result it's a little bit easier to see now which genres are particularly longer or shorter on average than sci-fi films we can get an even more pleasing effect.
But just by sorting this column by clicking on the column header there and that looks a little bit more interesting to me we can use the same basic technique to.
Replace multiple filters in the same measure to demonstrate this let's create a new page in the report and i'm going to add a matrix to that report page and i'll just reposition that and resize.
It and then in that matrix i'm going to display the genre field in the rows bucket the certificate field in the columns and then the original average runtime.
Measure in the values bucket i'll just increase the font size of the values again to make it slightly easier to read and then i'd like to create a new copy.
Of that same matrix which displays the average runtime sci-fi measure so let's copy the matrix and then paste it next door and then we can replace the average runtime measure with average runtime.
Sci-fi so you can see that for each column in the matrix each certificate is displaying the average runtime of science fiction films for that.
Certificate what i'd really like to do here though is create a unique combination of science fiction and pg films so that all of the cells in the entire matrix read the same value 117.71.
doing that isn't too difficult if i select the original measure that we've just created here average runtime sci-fi we can copy this.
Create a new measure and then paste in all the code i'll change its name so it's called average runtime scifi pg and then simply add a second filter to.
The calculate function so after genre equals science fiction i can say certificate equals and then pg once i've created that measure i can.
Swap out the average runtime sci-fi measure from that second matrix with the average runtime science fiction pg and we'll see that every single cell in.
The entire matrix reads the same value which is that same one there just as we did with the previous example then we can compare this measure with the original one by subtracting this.
Value from the average runtime so let's just quickly copy and paste our average runtime versus sci-fi measure save a tiny little bit of time and then we can create a new measure.
Paste in all the code we just copied and then just change its name to say average runtime versus sci-fi pg we then just need to make sure that we're comparing it against the correct.
Measure so average runtime sci-fi pg and then when we enter the result we can swap out the measure used in that second matrix to display the answer there's one additional complication with.
This one in that not every combination of genre and certificate actually produces a value for the average runtime so we've got quite a lot of negative 117.71 values here.
I think we should add an if function just to make sure that we're only calculating the result this result if the original average runtime is not blank.
So let's just add just this measure to say if and then we'll say not is blank and then refer to the original average runtime measure.
And as long as that condition is true then we'll calculate the average runtime minus average runtime sci-fi pg we don't need to provide the third argument if we don't provide the third.
Argument we'll return a blank which is exactly what i wanted so let's just update that measure and see that we return blanks there instead in terms of visualizing this i think.
Data bars in the matrix might be a little bit confusing let's use a background color instead i'm going to right click on the measure in the values bucket in the field well choose.
