Tag: DAX

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.