Posts

Power BI Licensing and Permissions

Image
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 mind this solution …

Attainment 8 in BI - part 2

Image
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 PointsDuplicating Combined Science records so a record of 65 became two records with points value 5.5Applying Discounting rulesAdding 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 two arguments a table, then an expression. What it does is it goes th…

Attainment 8 in BI - Part 1

Image
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 ‘Edit Queries’ from t…

The kids behind the numbers

Image
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",Students[KS2]>=4,"M",Stu…