Benchmarking against National Data

One of the features I was really pleased to add to Bromcom Analytics in BI was the ability to choose between various attendance and KS4 Performance benchmarks from national data. DfE publication is still a little scattergun (although it has vastly improved in the last couple of years) and if you hunt around a bit you can find national, local authority and school level performance at KS4, absence and persistent absence rates and Ofsted ratings. I've collated this data into a sheet you can download here. Be aware that it's 2016-17 data though as that was the latest available when I made it.

 My aim was to produce a drop down selector that allows the user to place a target indicator alongside their performance.

 
Green dotted lines show the selected benchmark. The Visual is from the BI Marketplace and called 'Data Bar KPI'

 Put National Data in the Cloud
To do this, I first uploaded my national data set to Google and converted it into a Google Sheet. Now as it's static data you might be wondering why not simply import it into BI? The reason is when you go to schedule refresh of your dashboard on the BI service, with static datasets you need to install a Power BI Gateway (even if those datasets are set to not automatically refresh in PBI desktop). It's an option but personally I think it's more hassle than hosting online.

 There's a little bit of wrangling to do once your spreadsheet is a google sheet though. First Share it to get a shareable link.
Your link will look like this:

 https://docs.google.com/spreadsheets/d/1qbEJU_gbBvSEsO6DorxXE0eEc1_09jGBvdHI418lmqQ/edit?usp=sharing

 Paste that into Notepad, we need to mess around with it a bit before it goes into BI. The string between d/ and /edit is the unique ID of the sheet, which I'll call the <UID>

 In notepad replace "/edit?usp=sharing" with "/export?format=xlsx&id=" then add the <UID> at the end. So my link would become:

 https://docs.google.com/spreadsheets/d/1qbEJU_gbBvSEsO6DorxXE0eEc1_09jGBvdHI418lmqQ/export?format=xlsx&id=1qbEJU_gbBvSEsO6DorxXE0eEc1_09jGBvdHI418lmqQ

 Then in BI select 'Web' from the Get Data dropdown and paste in the link.

 Summarising the Data with DAX

 Now that our national data is in BI we want to summarise it to give us averages for different Ofsted grades and school types. To do this click 'New Table' on the Modelling tab. The basis for this (and other) tables we're making here is the SUMMARIZE formula that has the syntax SUMMARIZE(<table to work in>,<column with group by values>, <column name1>,<summary expression1>,<column name2>,<summary expression2>....)

 My Ofsted Rating table looks like this:

 KPIOFS = SUMMARIZE(NationalData2017,NationalData2017[Overall effectiveness],"KS2APS",average(NationalData2017[KS2APS])/6,"9-4",average(NationalData2017[PTL2BASICS_94]),"9-5",average(NationalData2017[PTL2BASICS_95]),"EBACC",0,"Attainment8",SUM(NationalData2017[TOTATT8])/SUM(NationalData2017[TPUP]),"Progress8",average(NationalData2017[P8MEA]),"TPUP",AVERAGE(NationalData2017[TPUP]))

 What this formula is doing is exactly what the Table visual does in BI. The difference is that the data in our table is available for use in other visuals.

 (EBacc is '0' because I want space in the table for EBacc APS but there's no historical data)

 The Overall Effectiveness column has values 1 to 4. So I added a calculated column giving the values I want to present to the user:

 Benchmark = SWITCH(KPIOFS[Overall effectiveness],1,"Ofsted: Outstanding",2,"Ofsted: Good",3,"Ofsted: Requires Improvement",4,"Ofsted: Special Measures","Ofsted Ungraded")

 Different Tables, same Columns
Obviously you don't get overall national data by averaging individual school performance, they're published separately. But you want to arrange and import them so that they have exactly the same columns in the same order. Once you have a series of tables beginning KPI... that have data broken down by LA, Ofsted, overall national (and each with a Benchmark column, clearly saying what the row refers to) you need to combine them into a single table.

 This is simple, create a table with DAX and use the UNION function to simply list the tables you want to join.

 KPIUnion = union(KPIOFS,filter(KPISchType,KPISchType[MINORGROUP]<>blank()),KPILA,OverallNational)

 Note the optional filter to remove blank rows from table KPISchType

 Be aware when using UNION that all it's doing is stacking tables on top of each other without any clever data checking/matching. That's why it's important to make sure all your constituent tables have the same columns in the same order.

Comments

Post a Comment

Popular posts from this blog

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

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 t
Read more

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 ‘Edit Qu
Read more