PowerBIforSchools

Building visuals and dashboards is only part of what you need to do before BI can drive any improvement of your school. Once it’s done you need to get those dashboards in front of the people who will turn your data into action. This post explores the various options for publishing and sharing, working from the lowest cost/most restrictive option to more feature rich but expensive paths. Power BI for Free Power BI dashboards are designed using a windows program (Power BI Desktop) then published to a web server which is where you would expect users to interact with them. PBI Desktop is free to download and creates files with the extension pbix. It’s also free to create an individual Power BI account so any user can publish to the secure BI site app.powerbi.com – what they can’t do for free is share dashboards securely (more on that below). If you want a totally secure BI ecosystem without paying anything to Microsoft then you can do that by sharing PBI files but bear in m...

Previously on BI4Schools In a previous post we covered how to prepare exam data to work out Attainment 8. Specifically that meant: Adding QANs and ‘EBaccClass’ (EN, MA, EB or OP) Adding DfE Performance Points Duplicating Combined Science records so a record of 65 became two records with points value 5.5 Applying Discounting rules Adding an Index which uniquely identifies each grade ordered from largest to smallest. ·       We brought that data into a BI model with the table name A8Data and linked it to a students table on StudentId. Buckets of Joy We going to create 4 measures, one each for the 4 buckets that make up Attainment 8. We’ll tackle them in order of difficulty: Maths, English, EBacc then Open. Before we get started though I want to do a deep dive on a special class of formulas in DAX – <aggregate>X, specifically SUMX as understanding how that works is key to understanding the measures we need to create. SUMX SUMX has tw...

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

When we look at school data it's important not just to know the numbers/percentages achieving this or that but also to see the individuals that make up those numbers. Shout out to  @SJAPaine  for pointing this out and suggesting this post. If you have a topic you'd like me to cover, find me on twitter  @BI4Schools KS2 to KS4 Transition Matrix I'm building this using the Students and AssessmentResults tables from my sample dataset, which if you want to play around with you can download  here . The students table has a field 'KS2' with an average fine level (e.g. 4.1) and the AssessmentResults table has various KS4 outcomes graded 1-9. I didn't want to split my group into such fine categories so the first thing I did was classify KS2 prior attainment into 3 categories, High, Middle and Low. I did this by adding a calculated column to the Students table with the following expression: HML = SWITCH(TRUE(),Students[KS2]>=5,"H",Studen...