Blog

A quick way to get started with Microsoft Power BI is to connect to data in MS Excel spreadsheets. Spreadsheet data is easy to import and it means you can focus your efforts on dashboard design - prototyping ideas before sharing them with colleagues.

Here are five things to think about when using Microsoft Power BI with data stored in MS Excel spreadsheets:

Schools and academy trusts that want to use the data they collect to raise teaching quality and improve learner outcomes may face particular challenges.

The real challenge for any school that wants to use a tool like Microsoft Power BI to analyse and report on data is how to get at data that is spread across different application databases - you want a solution that doesn’t involve lots of copy and pasting between different spreadsheets.

How easy it to compare the earnings of graduates from different universities? Does a computer science graduate from Durham University make more money than one from the the University of Sheffield?

The Department for Education’s longitudinal education outcomes dataset is a good place to start. It’s a quick job to convert the data into an Excel table, fix the formatting on some of the columns (whole numbers rather than text) and then import it into Microsoft Power BI Desktop.

The Power BI Radar Chart is a nice way to show how a set of values varies across multiple categories. And you can add further sets - so one radar chart shows two or more sets of values against the spokes. You then see at a glance how the different sets of values compare.

I used the radar chart to compare ‘predicted’ and ‘actual’ attainment levels in different subjects for school students. The table of sample data is shown below. The attainment levels are in one column and the type - ‘Predicted’ or ‘Actual’ is in another column. I’m interested in the average predicted and the average actual attainment level for each subject category.

You can use Microsoft Power BI to turn your in-house data into interactive data dashboards and reports. It’s now one of the top two or three business intelligence tools on the market and it’s a great solution for schools that want to get more value from the data they collect about students and learning.

Power BI is very accessible - it’s easy for the non expert to get started - and it is not expensive. With Microsoft Power BI you can:

It’s easy to get hold of schools performance data about schools in England - data is available for download from the Department for Education website. This post uses the latest end of Key Stage data - the provisional dataset for 2016/17. You can find it here.

What’s the best way to visualise school performance data with Power BI? What is the best way to give the non expert - someone who doesn’t know too much about schools and statistics - an overview of school performance in their home town?

The scatter chart in the last Power BI blog post is nice but it doesn’t tell you everything you need to know about the relationship between % free school meals and GCSE performance at the end of Key Stage 4.

It seemed like a next step might be to calculate the Pearson correlation coefficient for the GCSE results/free school meals data.

A scatter chart in Microsoft Power BI a great way to the relationship between two values. You plot 2 sets of numbers as a series of xy points and then look for patterns in the data - trends, clusters and outliers.

The scatter chart below is a good example. The data, downloaded from the Department for Education website, is about school performance in 2015/16. It shows the percentage of pupils getting 5 grades A* to C (including English and Maths) at each school together with the percentage of pupils at the school who get free school meals. The source spreadsheet contains a lot of other useful data about schools including location - town, local authority and region.

This is one way you can use the cumulative sales total measure I described in the previous post. I’ve used a bar chart visual to show how the total value of course enquiries, enrolments and lost opportunities change over time.

This is the DAX for the course enquiries version of the measure and my breakdown of how it works is here: