Then we'll look at how to use the all selected function to remove all the filters from a single visualization and then how to use the all accept function to remove every filter from a measure.
Except for the ones that you specify so let's get started to get started i've created a new report and i've already imported all 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 if you want to follow along with the video and all i've done since importing the data is just tidied.
Up the data model a little bit and i've also made sure to hide all of the id fields from the various tables just to make our fields list a little bit neater and tidier when we're building our.
Table first i'm going to drag the region field into the rows bucket and then from the country table i'll drag the country field and sit that below region.
Then from the genre table i'll drag the genre field and drop that below country and then finally from the film table i'll drag the title field and drop that just below genre.
What i'd now like to do is create a basic measure that shows me the total run time for our films so to do that i'm going to go to the all measures table i'll right click and choose new measure.
And i'm going to call this one total run time let me just zoom in on the formula bar so we can see that a little more clearly so total run time and this is simply.
Going to be equal to the sum of the runtime minutes field from the film table and then close the round brackets and hit enter.
Posts Related:
- Magazine Mockup Pack Download In PSD Files Sheri Sk Magazine Mockup PSD
- How To Design A Draggable Contact Form Using HTML, CSS & JS Part 2 BlogSpot
- How to Make a Cover Slide In PowerPoint Office
And i can now just get rid of the delete
Me column to convert this table into a proper measures table so let's get rid of the delete me column and confirm that we want to do that.And just so that we can see the results of that measure let's drag it into the valley's bucket of the field well for that matrix and then i think i might want to just bump up the font size a.
Little bit to make this a little easier to read let's head over to the format pane and we'll search for the font formatting properties and i can bump up the values font size up to 13. let's.
Also go with the row headers font size that bump that up to 13 and maybe the column headers as well okay so having done all that if i just click away from the matrix things should.
Be a little easier to read i hope and then i'm just going to expand the last region here and then the country of new zealand and then the genre called fantasy so we can see a list of various.
Films and their total run times what i'd like to do next is show the total runtime for each individual film as a percentage of the total runtime of everything in this matrix visual.
In order to do that i've got to remove all of the filters that are applied to these individual values and you may remember from a couple of videos ago that there's a couple of ways we can do.
This we can either use the remove filters function or we can use the all function let's just have a quick look at how to do that i'm going to add a new measure.
To the all measures table let's call this one something like total runtime no filters i'm not going to hashtag it this time.
Though i'm going to use the calculate
Function and we're going to use our existing measure total runtime we could of course write our sum function in here again but.As we already have the measure it makes sense to use it and then to remove all the filters as i say we can either use the remove filters function.
Or we can use the all function instead so for this example i'm going to use the all function i'm not going to pass any values to any of its parameters so no arguments for the old function there.
Close around brackets for the calculate function and then hit enter to create it now if we just add this new measure into the matrix temporarily so we can see the end results just bear with this because.
It's going to make a bit of a mess of our matrix the important thing is we can see that each individual row is showing the exact same value the grand total of all the runtimes of everything in the.
Visual so if we can calculate that for every single row we can divide the runtime of an individual film against the grand total to find out the percentage.
Contribution so let's just remove that new measure from the matrix just to tidy things up a little bit and then let's add another new measure.
Which will divide the two that we've already created so let's call this one something like total runtime percentage.
No filters equals and then we'll use the divide function to divide our total run time the one that is affected by all the filters.
Against the total runtime no filters the one that isn't affected by any filters we'll create that new measure and then let's assign percentage formatting to that and then if we add our new measure.
Into the matrix so total runtime percentage no filters and then let's just change the the grouping gears let's just drill back up again to collapse everything and just.
Focus back on new zealand fantasy movies we can now see the percentage contribution each individual films run time makes to the grand total of the entire.
Visual next i'd like to add some slices to this page so that we can filter the results of the matrix even further so let's just click away from the matrix first and then add a slicer visual to.
The page and into this first one i'm going to add the oscar nominations field then i'll add a second slicer and to this one i'm going to assign the oscar wins field.
And then just before we start applying any filters i'd like to add a couple of cards to help explain what values are being displayed in this matrix so i'm going to add a card that will first of.
All show us our total runtime measure so that's the original one just the basic sum of runtime minutes so that one will be affected by all filters and then just below that let's add a.
Second card and to this one we'll assign the total runtime no filters so currently they both display the exact same values now if we look at the matrix if we just.
Pick a few of the figures displayed here we can see that this 100 in the bottom right hand corner is being generated by dividing a hundred and forty three and a half thousand i'll.
Abbreviate it to 144 000 by 144 000 so it's the total runtime of the filtered results by the total runtime of the unfiltered results we can take that down a level if we have a look here at the.
Value 0.74 percent this is being generated by dividing the number 1062 against the total runtime no filters 144 000 and similarly to an individual film.
Level the lord of the rings return of the king 0.14 is 201 divided by 144 000. so what happens if we start applying filters using our slicers what things in.
The matrix will change let's increase the oscar nominations value the minimum value up to one so we're now going to remove any films from the matrix that were not nominated for.
Any oscars so we'll see a few things will change our total run time filtered will give us just over 69 000 so we can see now that the grand total.
Percentage is generated by dividing 69 000 by 144 000 you'll see that the run time for an individual film of course hasn't changed the film's run time is the film's run.
Time that's unaffected by this oscar nominations filter so 0.14 this is still being generated by dividing two hundred and one by a.
Hundred and forty four thousand who's been divided by the unfiltered value not the filtered total finally in this particular example because all the films in this particular.
Category have received at least one oscar nomination we still retain all of these films in the group so the grand total run time for that filtered group is still 1062.
And we're still dividing that by the grand total runtime with no filters so the percentage is still 0.74 percent if we just do one more quick example here i'm going to increase the minimum.
Oscar wins up to one so now we're showing films in this matrix only those that have won at least one oscar so you can hopefully see this time we've reduced the number of films in this.
