Improved / Stayed Static / Regressed Assessment Measure
One of the weaknesses of traditional assessment tracking is that combining results from all collections into one large spreadsheet is cumbersome and so you often end up looking at each data drop in isolation. Power BI is much better at handling large data volumes and this post is about how to add as tooltips to a graph showing different cohorts' progress through KS4, the proportion of grades which improved, stayed static or went down from the previous collection.
You can follow along with my sample data here. I'm using a datasource called AssessmentResults where results are identified by AssessmentType, YearGroup, Term and Subject and the key result data fields are Result, ResultDate and Points (the numerical value of the result). I'll add my usual disclaimer that there could be more efficient ways of achieving this outcome and if so, please add them in the comments or ask to submit a guest post.
Cohort and Stage Columns
The first task is adding calculated columns to identify the cohort of the result recipient and the year and term of the result which I named the 'Stage'. You'll see that these are the legend and X-Axis of the graph in the screenshot above.
Cohort = if(month(AssessmentResults[ResultDate])>8,year(AssessmentResults[ResultDate])+(12-value(AssessmentResults[YearGroup])),year(AssessmentResults[ResultDate])+(11-value(AssessmentResults[YearGroup])))
Depending on whether the result is from Autumn or not, the cohort is a function of year of result plus 11 or 12 minus year group. E.G. in the autumn term of 2018, Year 11 are in the (2018+ (12-11)) = 2019 Cohort because they will finish KS4 in 2019. Note that this requires that assessments have a result date corresponding to the year in which they were taken.
Stage = if(AssessmentResults[AssessmentType]="Exam Result","Exam",Concatenate(if(len(AssessmentResults[YearGroup])=1,concatenate("0",AssessmentResults[YearGroup]),AssessmentResults[YearGroup]),CONCATENATE(": ",AssessmentResults[Term])))
Stage has a little wizardry because some people store their years 7-9 as 0X and some use X. This checks and adds the "0" if it's not there so that the stages order properly. You could remove the second if statement and just use the outcome that matches your school's data.
Stages Table
The Stages table is an index made from our existing data using DAX. (This is a really useful feature of PBI and it's worth checking out the DAX functions that return tables to see what the options are.)
To create a table using DAX go to the Modelling Tab and click 'New Table'.
You then enter a formula to create the table in the same way you would define a column or measure. In this case we want a table that returns the unique values of our calculated 'Stage' Column in the AssessmentResults table.
Stages = values(AssessmentResults[Stage])
This returns a one column table listing the unique values of Stage. We are then going to add a calculated column giving us a number for each stage from say 1, 09: Autumn to 9, Exam
We do this using the RANX formula:
Index = rankx(Stages,Stages[Stage],,ASC)
Stages[Index] gives us a way of working out for a given stage the one that comes before or after it.
Assessment Archive Table
We are now going to create another table using DAX (told you it was useful) mirroring our existing AssessmentResults Table. We're doing this because it's easier to look things up on a different table than within the same one.
Our AsmArchive table uses the ALL function to pull out all the unique records of the selected fields.
AsmArchive = all(AssessmentResults[SubjectSID],AssessmentResults[AssessmentType],AssessmentResults[Stage],AssessmentResults[Points])
(SubjectSID is a concatenation of Subject Name and StudentID) so this table has the minimum information necessary to uniquely identify all the results in the system, as Stage codes both the year and term of the result.
Make the Relationships
We're going to need to look up the stage index value of results in both AssessmentResults and AsmArchive so they need to be related to Stages. Do this on the Relationships page. For both the results tables drag Stage on to the Stage of table Stages.
Like this:
Add Stage Index to AsmArchive
Now that we have a relationship from Stages to AsmArchive we can add a field from one to the other using the RELATED function.
StageIndex = related(Stages[Index])
We'll need this numerical index value as a lookup reference.
Calculate the 'Move' Columns
We're now going to use calculated columns to show the result from the previous stage, which I've called 'MoveC' for the previous Current assessment and 'MoveP' for the previous Predicted assessment. The formula uses the LOOKUPVALUE function to retrieve the result from the AsmArchive table (which remember is a clone of the results in Assessment Results).
MoveC = LOOKUPVALUE(AsmArchive[Points],AsmArchive[AssessmentType],"HELM KS4 Current",AsmArchive[SubjectSID],AssessmentResults[SubjectSID],AsmArchive[StageIndex],related(Stages[Index])-1)
MoveP = same but with "HELM KS4 Predicted"
LOOKUPVALUE has the syntax ( <table where you're looking stuff up>, then pairs of lookup values first from the target table then either static (like "HELM KS4 Current") or from the source table. Note that RELATED is used to bring in the Stages[Index] value as it is on another table and that we subtract 1 because we want the result from the previous stage.
Why separate out C and P?
If you changed the formula to:
Move = LOOKUPVALUE(AsmArchive[Points],AsmArchive[AssessmentType],AssessmentResults[AssessmentType],AsmArchive[SubjectSID],AssessmentResults[SubjectSID],AsmArchive[StageIndex],related(Stages[Index])-1)
Then it would work fine - records with HELM KS4 Current Assessment Type would show the previous Current Result and likewise for HELM KS4 Predicted. The reason I have separated them out is I have results with Assessment Type 'Exam Result' and for those I want to see the previous Current and Predicted Grade.
Create the MovedUp, Static and MovedDown Measures
Finally, we're ready to create the measures that tell us how many kids have moved up, stayed static or gone down. This measure checks for records that have a previous result and only considers those.
CurrentMovedDown = sumx (AssessmentResults, if(and(not(isblank(AssessmentResults[MoveC])),AssessmentResults[MoveC]>AssessmentResults[Points]),1,0)/countrows(filter(AssessmentResults,not(ISBLANK(AssessmentResults[MoveC])))))
CurrentStatic and CurrentMovedUp then the ">" needs to change to "=" and "<" (remember that MoveC is the previous stage's result)
(The three Predicted Move measures have the same syntax but with MoveP replacing MoveC)
The SUMX formula carries out an expression on every row of the table (so is kind of like a more efficient way of adding a calculated column) and sums the results of those expressions. It's then divided by a count of the number of rows where the previous result is present.
The measures will be used in visuals where row context will filter to results of a particular stage and cohort.
You can follow along with my sample data here. I'm using a datasource called AssessmentResults where results are identified by AssessmentType, YearGroup, Term and Subject and the key result data fields are Result, ResultDate and Points (the numerical value of the result). I'll add my usual disclaimer that there could be more efficient ways of achieving this outcome and if so, please add them in the comments or ask to submit a guest post.
Cohort and Stage Columns
The first task is adding calculated columns to identify the cohort of the result recipient and the year and term of the result which I named the 'Stage'. You'll see that these are the legend and X-Axis of the graph in the screenshot above.
Cohort = if(month(AssessmentResults[ResultDate])>8,year(AssessmentResults[ResultDate])+(12-value(AssessmentResults[YearGroup])),year(AssessmentResults[ResultDate])+(11-value(AssessmentResults[YearGroup])))
Depending on whether the result is from Autumn or not, the cohort is a function of year of result plus 11 or 12 minus year group. E.G. in the autumn term of 2018, Year 11 are in the (2018+ (12-11)) = 2019 Cohort because they will finish KS4 in 2019. Note that this requires that assessments have a result date corresponding to the year in which they were taken.
Stage = if(AssessmentResults[AssessmentType]="Exam Result","Exam",Concatenate(if(len(AssessmentResults[YearGroup])=1,concatenate("0",AssessmentResults[YearGroup]),AssessmentResults[YearGroup]),CONCATENATE(": ",AssessmentResults[Term])))
Stage has a little wizardry because some people store their years 7-9 as 0X and some use X. This checks and adds the "0" if it's not there so that the stages order properly. You could remove the second if statement and just use the outcome that matches your school's data.
Stages Table
The Stages table is an index made from our existing data using DAX. (This is a really useful feature of PBI and it's worth checking out the DAX functions that return tables to see what the options are.)
To create a table using DAX go to the Modelling Tab and click 'New Table'.
You then enter a formula to create the table in the same way you would define a column or measure. In this case we want a table that returns the unique values of our calculated 'Stage' Column in the AssessmentResults table.
Stages = values(AssessmentResults[Stage])
This returns a one column table listing the unique values of Stage. We are then going to add a calculated column giving us a number for each stage from say 1, 09: Autumn to 9, Exam
We do this using the RANX formula:
Index = rankx(Stages,Stages[Stage],,ASC)
Stages[Index] gives us a way of working out for a given stage the one that comes before or after it.
Assessment Archive Table
We are now going to create another table using DAX (told you it was useful) mirroring our existing AssessmentResults Table. We're doing this because it's easier to look things up on a different table than within the same one.
Our AsmArchive table uses the ALL function to pull out all the unique records of the selected fields.
AsmArchive = all(AssessmentResults[SubjectSID],AssessmentResults[AssessmentType],AssessmentResults[Stage],AssessmentResults[Points])
(SubjectSID is a concatenation of Subject Name and StudentID) so this table has the minimum information necessary to uniquely identify all the results in the system, as Stage codes both the year and term of the result.
Make the Relationships
We're going to need to look up the stage index value of results in both AssessmentResults and AsmArchive so they need to be related to Stages. Do this on the Relationships page. For both the results tables drag Stage on to the Stage of table Stages.
Like this:
Add Stage Index to AsmArchive
Now that we have a relationship from Stages to AsmArchive we can add a field from one to the other using the RELATED function.
StageIndex = related(Stages[Index])
We'll need this numerical index value as a lookup reference.
Calculate the 'Move' Columns
We're now going to use calculated columns to show the result from the previous stage, which I've called 'MoveC' for the previous Current assessment and 'MoveP' for the previous Predicted assessment. The formula uses the LOOKUPVALUE function to retrieve the result from the AsmArchive table (which remember is a clone of the results in Assessment Results).
MoveC = LOOKUPVALUE(AsmArchive[Points],AsmArchive[AssessmentType],"HELM KS4 Current",AsmArchive[SubjectSID],AssessmentResults[SubjectSID],AsmArchive[StageIndex],related(Stages[Index])-1)
MoveP = same but with "HELM KS4 Predicted"
LOOKUPVALUE has the syntax ( <table where you're looking stuff up>, then pairs of lookup values first from the target table then either static (like "HELM KS4 Current") or from the source table. Note that RELATED is used to bring in the Stages[Index] value as it is on another table and that we subtract 1 because we want the result from the previous stage.
Why separate out C and P?
If you changed the formula to:
Move = LOOKUPVALUE(AsmArchive[Points],AsmArchive[AssessmentType],AssessmentResults[AssessmentType],AsmArchive[SubjectSID],AssessmentResults[SubjectSID],AsmArchive[StageIndex],related(Stages[Index])-1)
Then it would work fine - records with HELM KS4 Current Assessment Type would show the previous Current Result and likewise for HELM KS4 Predicted. The reason I have separated them out is I have results with Assessment Type 'Exam Result' and for those I want to see the previous Current and Predicted Grade.
Create the MovedUp, Static and MovedDown Measures
Finally, we're ready to create the measures that tell us how many kids have moved up, stayed static or gone down. This measure checks for records that have a previous result and only considers those.
CurrentMovedDown = sumx (AssessmentResults, if(and(not(isblank(AssessmentResults[MoveC])),AssessmentResults[MoveC]>AssessmentResults[Points]),1,0)/countrows(filter(AssessmentResults,not(ISBLANK(AssessmentResults[MoveC])))))
CurrentStatic and CurrentMovedUp then the ">" needs to change to "=" and "<" (remember that MoveC is the previous stage's result)
(The three Predicted Move measures have the same syntax but with MoveP replacing MoveC)
The SUMX formula carries out an expression on every row of the table (so is kind of like a more efficient way of adding a calculated column) and sums the results of those expressions. It's then divided by a count of the number of rows where the previous result is present.
The measures will be used in visuals where row context will filter to results of a particular stage and cohort.
Comments
Post a Comment