My company rents out rooms by the hour. I am trying to add a feature to my PBI utilization tracker that shows popularity by half-hour
We have a system that stores bookings with the following info:
Booking Start Date/Time
Booking End Date/Time
Through a series of calculated columns, I was able to track half-hours of utilization between the start and end time. (Ex: A booking with start time of 3:00 PM and end time of 4:30 PM would be tallied for 3:00 PM, 3:30 PM, 4:00 PM)
In the screenshot, we can see utilization merged by month.
What I am now trying to do is display the information vertically and ordered from most-used to least-used, while still tying it to a date range. Based on the screencap above, this data would ideally look like:
3:30 PM --- 6,819
2:00 PM --- 6,745
2:30 PM --- 6,674
1:30 PM --- 6,642
My hope is to display the top 10 most utilized half-hour blocks as a vertical bar graph, but the issue is that I cannot capture the data in one measure. Any help would be greatly appreciated!
Some additional info about the dashboard:
The bookings table is DirectQuery from SQL.
A calendar table is linked to the bookings table by booking date.
Thank you for your responses. Please click here to find my sample data for the dashboard for the month of August 2018. There's a .pbix in import mode, and an Excel with the data for the three source tables.
I am trying to replicate what is currently the 'Popularity by Hour' bar graph on the 'Utilization Dashboard' tab, but captured with a measure rather than each column being a separate value column. I believe this is necessary so I can manipulate the data more intuitively (specifically removing zeros, sorting by value and using a 'Top 10 feature').
Please see the 'Popularity by Half-Hour' tab for some more context on how utilization is being captured in units.