Cell or a column in a matrix we can use an if function to influence which values are included in the aggregate the sum on the average you may have already encountered this if.
You've watched video 9.11 in this series using some of countif average if expressions you may already know how to achieve this but just in case you missed that video and.
You wanted a quick look at a specific example to do with a matrix let's have a look at how to solve this problem so i've already set up a basic blank report and i've made a connection to my.
Standard movies database the next thing i'll do is create a quick data set including the fields i'll need so i'm going to right click the movies data source and choose to add a data set.
Table everything from the genre table and then from the film table just the runtime minutes field from there i can click ok.
And then ok again and there's my dataset created next i'll create a basic matrix so i'll right click into the background of my report and choose insert matrix.
In the rows box i'm going to choose to include the genre id field i want to base the grouping on the genre id then i'm going to change the value displayed in that cell to the name of.
The genre so i'll select the genre field and then i'm just going to change the sorting of my genre id group as well so in the rogue groups panel i'm going to right click and choose group properties.
We can see the grouping is based on the id but i'm going to go to the sorting page and change the sourcing so it's sorted by the genre name then i'll do a similar thing for the.
Posts Related:
- Properly Handling Date Grouping in Excel Pivot Tables (Change Grouping, Get All dates)
- Prezi style Powerpoint - Slide Zoom feature - Hyperlink
- OneNote Tutorial Office
Column groups i'll create a column group - ID Card Make
Based on the certificate id field and then i'll change the value displayed in there from certificate id to certificate.I want the sorting to be based on the certificate id so i'm not going to change anything in the column group then in the data cell i'm going to start by including the runtime minutes field.
And that will provide me with a sum of runtime minutes which is what i want and then just to make sure that i don't encounter this font rendering book which you may have seen yourself when you run.
A report and you don't see anything in the table or the matrix you've created so i've just switched from one from the standard font to a different font and then back again.
And then if i just run the report and make sure that i've got some sensible results there should be our basic matrix the two questions that i highlighted.
Earlier we're both concerned with comparing the values in specific columns of this matrix so for example we want to compare the values for the 12 and pg certificate films or indeed any other.
Combination of columns from the same matrix to help demonstrate this i'm going to begin by adding a total column to this matrix so a new column to the right hand.
Side that just shows the grand total run time for that entire genre now there are a couple of different ways we can do this back in the design view i could just right click on the data cell there.
The sum of runtime and choose add total column if i wanted to do this manually however i can also right click at the top of the column header choose insert column and.
The important thing that i must do here
Is choose outside group so i'm going to go outside group right and then i can select the runtime minutes field from the field selector again and that will.Basically do exactly the same thing as adding a total column so if i run the report at this point i'll get the grand total for each individual genre ignoring the grouping.
By certificate next i'd like to change this expression so that it calculates the sum of run time for just the pg certificate films and to do that we can nest an if.
Function inside the sum function we've just created so back in the design view we can right click on the sum of runtime minutes in our new column and then choose.
Expression to open the expression builder what i'd like to do is insert my if function just inside the sum function here.
Just to help with the layout of this i'm going to take this field of runtime minutes value down a couple of lines and then just below the sum function i'll type in a few spaces and then begin.
My if function i i f open up some round brackets on the next line i want to check if the value of the certificate field is equal to the letters pg.
So to do that i'm going to go to the fields list and double click the certificate field to insert that and check if it's equal to the letters pg included in some double quotes there.
If that's true i want to type in a comma and then i want to use the value of the runtime minutes field if that condition isn't true however i'm going to type in a comma at the end of.
The runtime minutes value what i want to do is use a value of either 0 or nothing now for a sum function it doesn't really matter too much whether we use a 0 or.
Nothing here but this will make a big difference if we're doing things like averages so rather than just using a zero i'm going to use the nothing so i've got the close round brackets for.
My if function i just need an extra set of close round brackets for my sum function so that's the entire expression created if.
We hit ok at that point and then run the report again we should now see that that column shows you the same value as for the pg certificate films for that specific genre.
So now if we want to compare pg certificate films with let's say 12a certificate films we just need another copy of that same sum if combination in the same expression.
So let's head back to the design view right click on the expression we've just created choose expression and then i'm just going to copy everything except for the equal sign there.
I'll type in a minus symbol after my first sum if combination and then paste in my new sum if combination on the next line all i now need to do is change the name.
Of the certificate i'm testing for so this time let's go with 12a and then if we click ok and run the report again we're now seeing a comparison of the pg.
Certificate films minus the 12a certificate films so comparing two specific columns isn't really that difficult once you've mastered nesting an if function inside.
Your aggregate function you can compare any two columns in this matrix but if we were trying to compare every other column of the matrix against the pg certificate it would be quite tedious.
To have to write a separate expression for every other column so let's try to be a little more clever about things to demonstrate this next bit i'm going.
To head back to the design view and i'm just going to modify the design of this matrix a little bit i'm going to change the width of the columns and i'd like to just create a copy of this matrix so.
That we can compare it with the new one that we're just about to create i'm just going to quickly copy and paste that matrix and place that side by side so we've just got two copies of the same.
Thing at this point now i'd like to change this first matrix so that within each certificate group i want to show the value for the pg certificate for that specific genre.
Let's try to do that back in the design view by using our sum if combination i'm going to right click on my runtime expression for our summary column and choose to view the expression builder.
Then let's just take the original sum if combination to show the sum of runtime for pg certificate films i'll copy that and then i'm going to go to the expression for the sum of runtime in the.
Main data cell of the matrix choose expression and then just replace the existing sum of runtime minutes with our sumif combination if we click ok then and run the report.
We won't see quite what you might expect the pg column does indeed still show the sum of runtime for pg certificate films but every other genre just shows a blank or nothing.
That's because each of these certificate columns has already filtered out any films which aren't part of that certificate so these individual columns don't have access to the pg certificate.
Films so it can't sum the runtimes of those films in order to make this work we'll need to modify the scope applied to our sum function so that each of our certificate.
