Attainment 8 in BI - part 2


Previously on BI4Schools

In a previous post we covered how to prepare exam data to work out Attainment 8. Specifically that meant:
  • Adding QANs and ‘EBaccClass’ (EN, MA, EB or OP)
  • Adding DfE Performance Points
  • Duplicating Combined Science records so a record of 65 became two records with points value 5.5
  • Applying Discounting rules
  • Adding an Index which uniquely identifies each grade ordered from largest to smallest.



·     We brought that data into a BI model with the table name A8Data and linked it to a students table on StudentId.

Buckets of Joy

We going to create 4 measures, one each for the 4 buckets that make up Attainment 8. We’ll tackle them in order of difficulty: Maths, English, EBacc then Open. Before we get started though I want to do a deep dive on a special class of formulas in DAX – <aggregate>X, specifically SUMX as understanding how that works is key to understanding the measures we need to create.

SUMX
SUMX has two arguments a table, then an expression. What it does is it goes through each row of the table, calculates the expression in that row and sums the result for all rows. In other words it’s like adding the expression as a calculated column to the table and using a normal SUM function on that column.

In this simple example the formula SUMX(Animals,IF(ISBEAR(Animals[Animal]),1,0)) would return the value 3 as the result of the expression would be 1 for Panda, Grizzly and Paddington and 0 for Dog. (Obviously ISBEAR is not a real DAX formula but you get the idea).

There are two really important concepts to understand here. The first is that the measure responds to row and filter context, so if you have a filter somewhere on your report, Fictional Character = False then the result will be 2 as only the rows Panda, Grizzly and Dog will be considered in the measure. The second is that the first argument, Animals, doesn’t have to be an unaltered table from the model – it can be any DAX expression that results in a table (e.g. FILTER, SUMMARIZE, UNION). If you want to check what table functions there are and what they do, go to the DAX Function Reference.

If you think about the sums we need to do to work out A8 we need to go through each kid and for each one add up some of their results, so we will be nesting SUMX formulas, for each row of the Students table, iterating through relevant rows of the A8Data table, before moving on to the next student and their A8Data records.

Our measures will look a bit like this:

TotalPoints = SUMX(Students,SUMX(A8Data,A8Data[Points])

This formula will work through the students table and for each student, it will add up the points on the A8Data table. If you create a table with students on it and add that total points measure you will notice something strange though. The totals for the individual students will be right, but the overall total will be far too high (it will be the total number of points in the table multiplied by the number of students).
The student totals are correct but the total, 556K for 98 students, is way off

The reason for this discrepancy is that when this measure is calculated within the context of a row with a specific student that Row Context filters the A8Data table so only points belonging to the child in that row are summed. The total is calculated without any row context, so all rows in the A8 data table are summed. To fix this we need to tell the measure to apply the row context from the students table to the A8Data using the EARLIER formula (see part one for more info on Row Context and EARLIER).

TotalPoints = SUMX(Students,SUMX(FILTER(A8Data,A8Data[StudentId]=EARLIER(Students[StudentId])),A8Data[Points]))

This won’t affect the results in the table with students (you’ve told it to apply a filter that row context was applying anyway) but it means that when the measure is calculated without row context, the A8Data table is filtered by the earlier row context of the Students table.

Maths Bucket

In A8 the maths bucket is simply the maths grade *2. We applied discounting rules in part 1 so we know that in our A8Data table each student has a maximum of 1 result where EBaccClass = “MA”.

Applying what we know about nested SUMX functions we can create the following measure:

MaBucket = SUMX(Students,SUMX(FILTER(A8Data,A8Data[StudentId]=EARLIER(Students[StudentId])&&A8Data[EBaccClass]="MA"),A8Data[Points]))*2

The measure goes through each row of the student table and gets the record for that student from the A8Data table with the EBaccClass 'MA'. That figure is multiplied by two and the results are summed for each student in the Students table.

English Bucket

The English Bucket is a touch more complicated as we need to work out the best of English and English Literature (or pick one arbitrarily if they are the same grade) and double it if both grades are present. Another way to think about this is that we want to take each student’s largest English grade and if they have 2 English grades, add the largest English grade again.

EnBucket = SUMX(Students,CALCULATE(MAX(A8Data[Points]),A8Data[EBaccClass]="EN")+
IF(CALCULATE(COUNTROWS(A8Data),A8Data[EBaccClass]="EN")=2,
CALCULATE(MAX(A8Data[Points]),A8Data[EBaccClass]="EN"),0))

The CALCULATE function allows to work something out with additional filter context (in this case the maximum points value where EBaccClass = “EN”). After working out everybody's maximum result, the IF function checks if two results are present, if so the maximum points are added again, if not zero is added. Existing row context is preserved which is why it works out the maximum for each student. Here there’s no nested SUMX formula so no need to preserve row context with EARLIER.

EBacc Bucket

Here’s where it starts to get interesting. The EBacc bucket is the sum of the best three EBacc qualifying subjects. To get at these we’re going to use the formula TOPN, which is a table formula that returns a table with a specified number of rows from a table in our model sorted in a specified way.

You might be thinking boom, TOPN(3,A8Data,A8Data[Points],DESC) and we’re done, but there’s a wrinkle. If you have a kid with EBacc results 7,6,5,5 then that formula will return all four records. TOPN returns all records with rank equal to or better than N, even if that means that the total number of records returned is greater than N.

This is why we created an index that uniquely identifies all the grades in the dataset in order of their points value. The formula 

TOPN(3,FILTER(A8Data,A8Data[EBaccClass]="EB"),A8Data[Index],ASC) 

can only return 3 rows because each Index value is unique. The sort order is ascending because we applied an index value of 1 to one of the grades with the highest points value and sequential numbers to all other grades in descending order of value. That means that in our set of 7,6,5,5 one of the 5s will have a lower index value and so will be included in this EBacc Bucket subtable. The other we will consider for inclusion in the open bucket.

EBBucket = SUMX(Students,SUMX(TOPN(3,FILTER(A8Data,A8Data[EBaccClass]="EB"&&A8Data[StudentId]=EARLIER(Students[StudentId])),A8Data[Index],ASC),A8Data[Points]))

Note that TOPN returns a table, which is why it can be used as the first argument of SUMX. Within TOPN there is another table argument where we use FILTER to ensure that only EBacc results are considered and that the earlier row context from table Students is respected.

Open Bucket

The Open Bucket is the most complex because as well as the subjects with a class of “OP” we also need to consider the unused English/English Lit grade and the 4th, 5th and 6th largest EBacc grade as well. We’ll create three separate tables within our measure for each of these sets of grades, then we’ll do the TOPN of that dynamically created table.

To get the second largest English grade we use the function EXCEPT which takes as its arguments two tables and returns all the rows in the left hand table that aren’t in the right hand table. We can therefore stick in EXCEPT(TOPN(2,….),TOPN(1,…)) and we will get just the second biggest result. Again we’ll need to sort on Index to ensure we’re identifying the right number of results in each table.

We’ll use exactly the same technique to get the EBacc results only for those it will be TOPN(6,..),TOPN(3,…).

The OP results are easy – FILTER returns a table, so for them we just need to 

FILTER(A8Data,A8Data[EBaccClass]="OP"&&A8Data[StudentId]=EARLIER(Students[StudentId]))

To combine our three tables we’ll use the UNION function. Union takes multiple tables as arguments and makes them one table. All the tables need to have the same columns, which isn’t an issue for us as they are all A8Data filtered one way or another.

Below is the full Open Bucket Measure – I separated out its parts on different lines to make it easier to maintain (you can do this by pressing Shift and Enter in the formula bar). 

Lines 4 – 6 are the tables that are combined in the UNION expression. Line 7 is the ordering used by the TOPN function from line 2. Line 8 (points) is the expression we want added up by the SUMX function from line 1.

1 OPBucket = SUMX(Students,SUMX(
    TOPN(3,
    UNION(
        EXCEPT(TOPN(2,FILTER(A8Data,A8Data[EBaccClass]="EN"&&A8Data[StudentId]=EARLIER(Students[StudentId])),A8Data[Index],ASC),TOPN(1,FILTER(A8Data,A8Data[EBaccClass]="EN"&&A8Data[StudentId]=EARLIER(Students[StudentId])),A8Data[Index],ASC)),

  EXCEPT(TOPN(6,FILTER(A8Data,A8Data[EBaccClass]="EB"&&A8Data[StudentId]=EARLIER(Students[StudentId])),A8Data[Index],ASC),TOPN(3,FILTER(A8Data,A8Data[EBaccClass]="EB"&&A8Data[StudentId]=EARLIER(Students[StudentId])),A8Data[Index],ASC)),

  FILTER(A8Data,A8Data[EBaccClass]="OP"&&A8Data[StudentId]=EARLIER(Students[StudentId]))),

    A8Data[Index],ASC),
    A8Data[Points]))

Attainment 8 Measure

To get attainment 8 you just need to sum all four buckets:

Attainment8 = [EnBucket]+[MaBucket]+[EBBucket]+[OPBucket]

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