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

 Set the 'Cross filter direction' property to 'Both', now the visual works. 

Note that the matrix shows the number of students with results for each input/output so the same student can appear in more than one columns (if they have a 4 in History and a 5 in English say). The total column shows the number of students not the number of results, which is why the row totals are less than the sum of the numbers in each row. If the visual is filtered to a specific subject, assessment type, year and term so that each student has only one assessment result then the total will equal the sum of the row values.


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


Adding Slicers

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, 
               "Order", INTEGER 
               ,{ 
                        {"H",1}, 
                        {"M",2}, 
                        {"L",3}, 
                        {"N",4}       
                } 
           ) 

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.

2.    Drillthrough

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.
Moving On

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


Comments

Post a Comment

Popular posts from this blog

Attainment 8 in BI - Part 1

Image
It’s been bugging me for a while that I didn’t know how to calculate A8 in BI, even though as a mostly Bromcom user it wasn’t a big deal as you can get the figures straight out of the MIS. That said, it was interesting solving all the steps along the way and even if you do get these figures out of your MIS or an Excel broadsheet I think you’ll find the exercise useful for building your BI skills. Source Data I’m going to assume source data in the form of a table of exam results with a student identifier, season, result date, exam code and result. You can download my source data  and/or the PBIX file . In a BI4Schools first we’re not going to import this data as is though, as we’ll be amending it using Power Query. Power Query (PQ) is the set of tools BI uses to get and manipulate data before placing it as a table in the BI model. PQ uses a language called M to do this – note that it is different to DAX, the language used within the BI model. To open Power Query click...
Read more

Subject - Class - Pupil Table

Image
One of the really powerful features of BI is being able to drill very quickly from the macro picture to the micro. To help schools do this with assessment results I've recently been working on this table showing subject, class and pupil level results (p2 of the report below). You can download the PBI file here , the class information looks like this  In a previous post I described concatenating studentid and subject to link classes, assessments and students, but that's not actually best practice - you can do it more simply with this setup I created the Subjects table using Dax (create table on the modelling tab). The formula is  Subjects = DISTINCT(AssessmentResults[Subject]). It gives a one column table with each distinct value of subject.  Note that here it is assumed that StudentClasses[Subject] isn't always the same AssessmentResults[Subject], as for example English Classes will often have English Literature Results. To handle...
Read more

Data modelling for a large MAT

Image
The wrong way When I first started developing in BI I worked on single schools and MAT demo systems with tiny numbers of students so my approach to data modelling was chuck data in however it comes then play with relationships until the configuration works. When I switched to developing for a large MAT (c40k kids in 50 schools) I found out the hard way the limitations of this approach-my measures and reports were unusably slow.  As well as the sheer volume of data I had problems because the MAT wanted a report on pupil demographics, attendance and exclusions with historical accuracy- ie not only seeing data for who's FSM, PP etc today but for any point/period in the past as well. This divides your pupil contextual factors into two types; unchanging, like ethnicity, EAL & KS2 prior attainment that don't change and so can stay on the students table, and changeable, like Pupil Premium eligibility, that can vary depending on the date range you're looking at.  My original ...
Read more