PowerBIforSchools

A colleague recently asked me if I could produce this in BI: This is a classic example of when schools resort to using Excel as very often in an MIS you would need to run/filter several reports to get the breakdown of each group. To do this I first downloaded the most recent national data from the DfE  and then did some copy and pasting in Excel to put it into usable form. You can download my file here , it looks like this: (As far as I know, there isn't a standard DfE data feed that can pull this data automatically - please tell me if I'm wrong about this!) OK so I imported this national data into BI and called it AbsenceGroups I use the following measures to work out pupil absence and persistent absence (see the previous post for more details on Persistent): Absences = sumx(StudentAttendances,if(or(StudentAttendances[MarkMeaning]="U",StudentAttendances[MarkMeaning]="A"),1,0))/sumx(StudentAttendances,if(StudentAttendances[MarkMeani...

One of the challenges I've recently faced is how to get a measure to show persistent absentees (students with <90% attendance), that will respond to any time period on a date slicer. If you want to follow along with this example then you can access a sample data set  or if you just want to visualise I will refer to two tables:  Students - a list of students StudentAttendances - a list of attendance records, with one row per student per session that includes the mark meaning (P - present, E - Approved Educational Activity, A - Absent, U - Unauthorised, C - Cancelled). Students is related to StudentAttendances with a one-to-many relationship. Step 1: Present and Possible My first step was to create two columns on the StudentAttendances table.  Present: = if(or(StudentAttendances[MarkMeaning]="P",StudentAttendances[MarkMeaning]="E"),1,0) Possible  = if(StudentAttendances[MarkMeaning]="C",0,1) Step 2: Attendance% measure Next I created...

Lots of schools and MATs are starting to use Microsoft Power BI as way of interrogating and visualising the data from their MIS systems. BI is great for this and has enormous potential to distill lots of noisy data into simple indicators for time-poor school leaders, but it's also tricky to learn and has the potential to become a beast to maintain for (just as time-poor!) Data Managers. The aim of this blog is to provide resources to help anyone using BI in education and to hopefully become a community of users who can share ideas and help each other out. I'd love to hear from, and host guest posts from anyone using BI for their school or MAT. If you'd like to get involved please contact me in the comments or at ed@rsic.co.uk I'm the Head of Analytics at Bromcom so my day job involves developing resources for schools and MATs using BI and Bromcom's other advanced reporting tools, but this blog is something I'm doing in my own time, welcoming to users of all ...