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.

 

 

 

 

 

Author: Gary Henderson

Gary Henderson is currently the Director of IT in an Independent school in the UK.Prior to this he worked as the Head of Learning Technologies working with public and private schools across the Middle East.This includes leading the planning and development of IT within a number of new schools opening in the UAE.As a trained teacher with over 15 years working in education his experience includes UK state secondary schools, further education and higher education, as well as experience of various international schools teaching various curricula. This has led him to present at a number of educational conferences in the UK and Middle East.

Leave a comment