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.