Power BI in schools - a student performance dashboard
This is another look at how you could build a very simple Power BI report to track student performance.
There are two tables of data – both imported into Power BI Desktop from an Excel Workbook. Power BI Desktop automatically joins the two tables on StudentId:
Two slicers on the report give us filtering by Current School and Form Group. Just like in one of the earlier examples, we can look at average student attainment in each subject.
Two almost identical measures are used to calculate the average current attainment level and the average target attainment level. This is the Target_Level measure:
Both measures use CALCULATE to evaluate an expression (calculating the AVERAGE level) after a filter has been applied – either Type=”Current” or Type=”Target”.
There’s an important point to note. CALCULATE operates within the current context – but then applies an extra filter on the Attainment[“Type”] field. That means it works when added to a table of students – it will calculate the average level for each student. It will also work when used on a bar graph with subjects – it will get the average level within each subject.
A third measure – Distance_From_Target – is simply the difference between the two other measures.
We’ve used the Distance_From_Target measure in a table visual to give a list of the biggest under performers. The visual level filter set on the Distance_From_Target measure in the visualisations panel means we only see students in the Students – Under Performers’ visual who are more than 1.5 below target.
There are a few reasons why I like simple reports like this:
- Firstly, user experience matters – we want users to think about the data. Nobody should struggle to understand how the dashboard works.
- A low testing overhead – it doesn’t take long to double check that the measures are working as expected. You can go back to the Excel source data, do some quick calculations and compare with the Power BI report.
- The report is focused on one thing – how students are doing – but it does lead you towards some more questions that could be answered with further reports. For example, do we see more under performance in certain subjects? We want our users to get more engaged without becoming lost in the data.