The kids behind the numbers
When we look at school data it's important not just to know the numbers/percentages achieving this or that but also to see the individuals that make up those numbers. Shout out to @SJAPaine for pointing this out and suggesting this post. If you have a topic you'd like me to cover, find me on twitter @BI4Schools
KS2 to KS4 Transition Matrix
I'm building this using the Students and AssessmentResults tables from my sample dataset, which if you want to play around with you can download here.
The students table has a field 'KS2' with an average fine level (e.g. 4.1) and the AssessmentResults table has various KS4 outcomes graded 1-9.
I didn't want to split my group into such fine categories so the first thing I did was classify KS2 prior attainment into 3 categories, High, Middle and Low. I did this by adding a calculated column to the Students table with the following expression:
HML = SWITCH(TRUE(),Students[KS2]>=5,"H",Students[KS2]>=4,"M",Students[KS2]>0,"L","N")
I then added a Matrix visual and set the row to be my new HML field, the columns to be AssessmentResults[Result] and the value to be a count of student ID.
This is the result and a quick look tells you that this is wrong. The reason is that by default BI creates relationships that filter in a single direction. Here assessment results are being filtered by students (which is why each row has a different total) but students are not being filtered by AssessmentResults (which is why each column has the total value in it).
We fix this on the Model screen, by double clicking the relationship and setting it to filter both ways.
Filter direction is Students to AssessmentResults only, double click the relationship
Percentage not Count
I copied and pasted my matrix and made the second one show percentages by clicking the menu next to the value field (count of Student Id) and choosing ‘percent of row total’ from the ‘Show Value As’ menu.
When you use this method 2 decimals places is the default and can't be changed. If you want to show a percentage with fewer dp then replace 'Count of StudentId' in the value field with a new measure (right click on a table on the right to create a new measure), with the following expression:
RowPercent = COUNTROWS(Students)/CALCULATE(COUNTROWS(Students),ALLEXCEPT(Students,Students[HML]),ALLSELECTED(AssessmentResults[Result]))
If you select this measure you can adjust its format and decimal places via the Modeling tab.
For more info on the CALCULATE function and how this measure works, see this post
Note that if you use a measure instead of a Count of StudentId drillthrough (below) won't work
Now the matrix works, I added slicers to let me choose the Subject, Term and Year of the KS4 assessment (without these results from all subjects, terms and years are shown together).
I prefer the dropdown style of slicer which you can select using the down arrow on its header. I also prefer titles to headers so after changing the type to dropdown I hide the header and add a title using the formatting pane.
Correct ordering of HML
Final bit of housekeeping is to sort out the ordering of HML which we obviously don’t want alphabetically. You might think the easiest way to do this is to assign them an order using IF/ SWITCH but you can’t sort a column by another column that’s derived from it by formula so that way requires having the sort order on your visuals.
I prefer to create a table with the sort order, by selecting New Table from the Modelling Tab then using the following DAX:
SortHML = DATATABLE("PAband", STRING,
The DATATABLE function is better than import from excel because it doesn’t interfere with scheduled refreshes (which require access to any excel sources) and it’s better than entering the data because it’s easier to edit later if necessary.
Create a relationship between SortHML[PAband] and Students[HML]. Then create a calculated column on students:
HMLOrder = RELATED(SortHML[Order])
This brings through the sort order. Now select Students[HML] on the field list and choose ‘Sort by Column’ from the Modeling tab. Select ‘HMLOrder’. Now the visuals will sort correctly.
Kids Behind the numbers
We’re going to look at three ways at seeing who the kids are behind the numbers. Cross filtering – where a list on the same page is filtered, Drillthrough, where you right click on a number and go to a different page and Tooltip page, where the list appears when you hover over with the mouse.
1. Cross Filtering
By default in BI, all visuals on the same page respond when you interact with one of them. So if you add a table with Student names, Year group, whatever contextual info you want onto the page. When you click a number in the matrix the table will be filtered to those individuals.
Above the 4 who have an input of L and output of 7 is clicked. The table on the right is filtered to these four students. This method is easy and intuitive for the user. The downside is that the student list takes up a lot of space on your dashboard.
An alternative method is to have the list of students on a completely separate page. This allows us to have a bigger table without worrying about space constraints.
Create a new page and add the table with the info you want. In order to make it a drill through page now drag the field used to identify the students (Students[StudentId] in my case) to the ‘Drillthrough’ section of the Visualizations bar.
When you have done that changed ‘Allow Drillthrough when: used as a category' to Summarized (because we want to drill through from a visual when many StudentIds are summarised as a count).
Now if you return to the original page and right click on a number in the matrix you’ll see a drillthrough option that takes you to the drill through page. BI will automatically create a back button for users to return, which you should incorporate into your design (or delete) as necessary.
Note that drillthrough only works when we have the drillthrough field in the visual. If we used a measure instead (e.g. StdCt = COUNT(Students[StudentId]) then the values in the table would be the same but drillthrough wouldn’t be available.
The downside of Drillthrough is that it’s slow to review multiple categories of students.
3. Tooltip page
Our final method is having the list appear as a ‘tooltip’ (ie a box that appears when you hover over the numbers with the cursor).
To do this create another new page. Before adding any visuals click the formatting option of the page (the Paint Roller below the Visuals). Turn the Tooltip property on and choose a size.
Now add the table with the student data to the page.
Return to your main page and select the Matrix. Click the paint roller to access its formatting and turn on tooltip and select your tooltip page. Now when you hover over a number you see the kids.
Note that there’s no way to scroll down the list, so your page needs to be big enough to list everyone. If the ‘Tooltip’ page size is too small you can make it bigger on the page settings by choosing a custom size. To access the page settings of the tooltip page click on the background of the page, then the paint roller.
Here’s my dashboard, if you want to download the pbix you can find it here.
If you have any questions about these steps please ask them in the comments and I’ll clarify. If there are topics you’d like me cover or general BI questions hit me up on twitter @BI4Schools