Power BI in schools - a student performance dashboard

31/01/19

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:

students and attainment

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.

student tracking report

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:

student target 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.

Difference in levels measure

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.

Difference in levels measure

There are a few reasons why I like simple reports like this:

  1. Firstly, user experience matters – we want users to think about the data. Nobody should struggle to understand how the dashboard works.

  2. 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.

  3. 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.

Power BI in Schools - save time, organise data and improve school performance

Find Out More