PowerBI and percentages

Was playing around with PowerBi the other day and found myself with a challenging situation.   Basically, I had a number of records for events, however wanted to know what percentage of available slots were booked and which were free, plus wanted this to be displayed in a pie chart.

The issue I had is the only records I had for each day were for booked slots, and I didn’t want to try and pre-process the data to add in records for un-booked slots, although this would have been one method I could have used to solve the issue.

I needed a way to get to the percentage of a fixed number of slots booked.     The answer it turns out was to create a New Measure in PowerBI (Right click in the Fields window and select New Measure or use the Modelling menu and then select New Measure) and to use a little bit of DAX, something I hadn’t really used before, to calculate the required data.

So, the measure I used is shown below:

Booked = COUNTROWS(Data)/(DISTINCTCOUNT(data[Resource])*50)

Data is the name of the table from which the info is coming from while Resource is the attribute/field listing the various bookable resources.   CountRows(Data) gives me a count of the number of records subject to any filtering which might be applied by the user of visual/page.   By using DistinctCount(Data[Resource]) I am only counting resources which have at least 1 booking.    Each resource has 50 possible slots which can be booked hence multiply by 50.   This returns a value between 0 and 1 representing the percentage of slots booked.

In order to create my PieChart I also need to know the percentage of a resource, which hasn’t been booked.   This is easy as it is simply 1 minus the booked value so could be calculated as below:

NotBooked = 1 – (COUNTROWS(Data)/(DISTINCTCOUNT(data[Resource])*50))

Now I can easily add the Booked and NotBooked measures to a pie chart and get my required chart like below:

I suspect this is me only starting to scratch the surface of what DAX might be capable of so I look forward to experimenting a little more with it in the coming months.

 

 

 

 

 

Microsoft PowerBI

Microsoft PowerBI is an excellent tool for use in presenting and analysing school data, allowing staff to explore and interact with data which traditionally may be locked away in complex and very flat spreadsheets.

Schools have access to a massive amount of data.   This includes information about each student, academic data from assessment and testing, or from professional judgments made by teachers.    Secondary schools will also have baseline data such as the Centre for evaluation and monitoring (CEM) MIDYIS or ALIS data.   You will have data on attendance and on where students have been acknowledged for their efforts, or where they have had to be warned regarding poor effort or behaviour.  The above only scratches the surface of the available data.   For me this has long been a challenge in that all of this data is usually in difficult to read spreadsheets, where without well developed skills in using excel for example, trends and patterns will not be easy to identify. Even with well developed spreadsheet skills, attempts to analyse and interpret will be time consuming.  In addition it is often extremely difficult to bring together data sets such as looking for possible links between academic data, behaviour, attendance, etc.

PowerBI allows you to take all of this data and start exploring it.    You can create reports which present the data in simple graphical form however allow for the data to be explored.    For example you might display the count of behaviour issues by gender.   Clicking on a given gender would then filter to this gender, thereby allowing you to see other graphs such as academic performance or attendance by the selected gender, but also still showing the full cohort average, thereby allowing you to see where a particular subset of students vary from the average.

The above example shows how PowerBI displays focus on a given subset of data within graphs.  The dark pink bars relate to the selected focus whereas the light pink show dark for the whole data set.

Clicking other graphs would then allow you to easily explore other subsets of the data.   You can create reports allowing filtering by SEN status, native language, gender, subject, year and any other fields for which you have data.

PowerBI also comes with its own analytics engine which will analyse your data and identify where subsets of your data deviate from the average.     It is clear Microsoft are continuing to develop this functionality as when I first used this it identified correlations which were obvious and therefore of little use however more recently when I have used the analytics it has identified some more useful correlations.   I suspect this area will be further developed over time bringing greater potential for how it could be used.

The one drawback with PowerBI at this point is licensing.    For free you can create your PowerBI reports for individual use or can share these as files for viewing in the Desktop application complete with full editing rights however the main potential as I see it is to centrally create PowerBI reports and share them via Sharepoint so that staff can access as and when required but without the ability to change the report and without the complexity of the desktop applications interface.   You basically present them with a web page of the data for staff to interact with and explore using the graphs and other visuals and filtering provided by the person who creates the report.   For this Microsoft are currently charging a per user per month cost.      Given the potential power which PowerBI could provide to schools my hope is that Microsoft will eventually reconsider this and make PowerBI more affordable for use by schools.

PowerBI for me is about putting school data in the hands of staff in a way that is quick and easily to interpret plus usable.   It is about being able to explore data by simply clicking on individual elements and about using the data we already capture more efficiently.    With carefully crafted reports, generated through discussion with staff, the time taken to manage and analyse school data can be reduced, yet staff can be empowered to know and use the available school data appropriately.   If you haven’t tried PowerBI with you school data I would recommend you give it a try.

 

Thoughts from the Bryanston Education Summit

I attended the 2nd Bryanston Education Summit during the week just past, on 6th June.   I had gone to in the inaugural event last year and I must admit to having found both years to be interesting and useful.   The weather both years has been glorious which also helps to add to the event and the beautiful surroundings of the school.   Here’s hoping Bryanston keep it up, and run another event next year.

During the day I attended a number of different presentations on different topics so I thought I would share some of my thoughts from these sessions.

The first presentation of the day was from Daisy Christodoulou who was discussing assessment.    She drew a really useful analogy in comparing preparing students for their exams with preparing to run a marathon.    It isn’t something where you can jump straight into a marathon distance on day 1 of training.  You need to slowly build up your preparations, focusing on developing certain skills and approaches.   You need to have a plan and then work to this plan, although amending it as needed as you progress, should injury arise or due to weather conditions, etc.    I found myself wondering about how often we actually spend with our students in discussing this plan, the proposed goal of the subject or year and how we will all, teachers, students, support staff and others, work towards those goals.

Daisy also spent some time discussing summative versus formative assessment suggesting that the use of grades should be kept to a minimum of only once or twice per year.   My first reaction to this was concern as it seemed to disregard the potential benefits of spaced retrieval testing which ultimately would result in a score representing the number of correct answers.   Following further thought my conclusion was that spaced retrieval is very focussed on knowledge plus just indicates where an answer is right or wrong as opposed to grading which is more a judgement of students ability.   As such it may be possible to reduce overall summative assessment grading while still making regular use of testing of student knowledge.   I think this also highlights the fact that assessment and testing are actually different things even although they are often generally used as two interchangeable terms referring to the same thing.

Mary Myatt was the second presenter who discussed how we might make learning high challenge but low threat.    As she discussed Sudoku I couldn’t help but draw parallels with computer gaming.  In both case we engage, of our own free will, in a form of testing.   In both cases the key is the low threat nature of the testing.    For me the question is therefore how do we make classroom learning and assessment low threat.    Mary suggested a path towards this in discussing with students our expectations such as setting reading outside their current ability level, which is therefore challenging, but telling them this and then promising to work through it with them in future lessons.   I think this links to building an appropriate classroom culture and climate such that students feel able to share the difficulties they face and work through them with the class.  It is very much about developing an open culture and positive or warm climate in which mistakes and difficulties are not seen as something to be feared or embarrassed by, but to be embraced, shared and worked through together.   Another thing I took away from Marys session was a list of books to read;  My bookshelf will be added to with some of her recommended books shortly.

The third of the sessions which I found most useful was the session by Andy Buck.    He discussed leadership drawing a number of concepts from the book Thinking Fast and Slow by Daniel Kahneman, a book which is one of my favourites.     I particularly enjoyed the practical demonstrations where he evidenced how we all show bias in our decision making.  This is a fact of being human and the way the brain works, we bring to decision making processes assumptions and viewpoints based on previous experiences, upbringing, etc.   He also, linked to this, demonstrated anchoring, managing to influence a whole room of educational professionals to get a question in relation to the number of Year 11 students in the UK wrong.   Statistics suggest that a percentage of the audience should have got this question correct based on a normal distribution of responses however using anchoring Andy influenced the audience away from the correct answer.   I have since used a very similar approach in a lesson with Lower 6 students to show how easily I can influence their answer and to suggest that Google, Amazon, Facebook, etc. with their huge amounts of data on individuals may therefore be able to influence individuals to a far greater extent.

There was also a presentation on VR in education which has opened my mind up a little to the possible applications of VR.   This might therefore be something we experiment with at school in the year ahead.

Microsoft’s Ian Fordham presented on the various things Microsoft are currently working on.   I continue to find the areas Microsoft are looking at such as using AI to help individuals with accessibility and in addressing SEN to be very interesting indeed.   I also was very interested by his mention of PowerBI as I see significant opportunities in using PowerBI within schools to build dashboards of data which are easy to interrogate and explore.    This removes the need for complex spreadsheets of data allowing teachers and school leaders to do more with the data available however with less effort or time required.    I believe this hits two key needs in relation to the data use in schools, being the need to do more with the vast amounts of data held with schools however the need to do it in a more efficient way such that teachers workload in relation to data can be reduced.

I also say a presentation by Crispin Weston on data use in school.    His suggestion that we need to use technology more to allow us to more easily analyse and use data is one I very much agree with.   This partly got me thinking about the Insights functionality in PowerBI as a possible way to make progress in this area.   He also talked about causation and correlation suggesting his belief that there is a link between the two and that the traditional call that “correlation is not causation” is in fact incorrect.   At first I was sceptical as to this however the key here lies in the type of data.    Where the data is simple and results in a simple linear trend line the resulting reliability of an argument that correlation equal causation is likely to be very low.   The world is seldom simple enough to present us with linear trends.    If, however the data over a period of time varies significantly and randomly and the second data element follows this however the reliability that correlation equals causation is likely to be significantly higher.     I think the main message I took away from Crispins session was to take data and findings with a pinch of salt and to ensure that context is taken into account.  If it looks simple and clear then there is something which hasn’t been considered.

Overall the day was a very useful one and the above is a summary of just some of the things I took away.   I must admit to taking 5 or 6 pages of tightly written notes, hastily scribbled on an iPad during the course of the day.

I hope that Bryanston decide to repeat the conference next year and is the quality of presenters and their sessions continues, that it becomes a reliable yearly event.   Here’s hoping the trend of good weather also continues should they decide to run the summit again next year.

 

 

 

PowerBI and School Data

Ever since I started playing around with PowerBI I have found it to be very useful indeed and I must admit that I am most likely only scratching the surface.

I came to experiment with PowerBI to try and address some issues I see with data management.    School data is often presented in colour coded spreadsheets showing student performance against baselines for example.   Different sheets are used to present different views on the data such as showing the performance by subject, by gender or the performance of students by SEN status or by EAL status.   Each additional view on the data, of which there are very many, presents us with another sheet of data.  The data is often presented as flat tables of figures however in some cases may involve pages upon pages of different graphs and charts each showing different views on the available of data.   The logic here being that each additional view on the data gives us more data that we can interpret and therefore a greater opportunity to draw insightful conclusions and from there develop actions.   I believe the reality is the reverse of this.

My belief is that teachers and heads of department don’t have a lot of time to analyse and interpret data, and therefore presenting them with so much data is counterproductive.  Having so many different views on the data presented at once also is difficult to process and to understand.   This in turn leads to either ignoring the data altogether or to giving it only a very cursory glance.   For those that love data it may lead to excessive amounts of time spent poring of the data, to data overload, where time spent planning actions, as opposed to analysing data, would be more productive.    As such I subscribe to the belief that “less is more”.

This is where PowerBI comes in.    PowerBI allows me to take my mountains of spreadsheet data and present it in a very easy to digest graphical format where each of these graphs and charts are interactive.    In PowerBI rather than one sheet by subject and another sheet for gender based data, you have just one set of graphs and charts.   You would just click on a gender or select a gender and all the graphs will change to show the results for that gender.   You might then click an SEN status to see how students who are male with SEN needs are doing compared to students on average.    This means we can combine all our different views which are normally represented by different sheets on a spreadsheet into a single set of graphs and charts.   The user then accesses the various views of the data by clicking on and through these graphs and charts.

The benefit of PowerBI is the ability to dynamically manipulate and explore the data by clicking through various graphs and filters.   You develop an almost tangible feeling for the data as you explore through it.   This is something that flat spreadsheets, even if graphs are included, lack.   Also, as you have less to look at, in one set of graphs rather than pages and pages of them, you have more time to explore and engage with the data.

The one current drawback to PowerBI is simply cost.   It is free to use as an individual both web based or via a desktop application, and you can share via sharing desktop app developed BI files however if you want to share via the web platform or if you wish to publish internally via SharePoint you will need a Pro license for each user.    Where you are sharing with a large number of users, even at educational pricing, this can become expensive.   Hopefully this is something Microsoft will be looking at and can resolve in the near future.

Schools continue to be sat on mountains of data.    PowerBI is a tool which allows us to present this data in a more user-friendly form which then allows it to be easily explored and manipulated, allowing more time to plan actions and bring about continuous improvement.  If you haven’t already done so I definitely recommend putting some of your school data in PowerBI and having a play with its capabilities.