Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi PBI pros,
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:
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)
[See screenshot]
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
.
.
.
etc.
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:
Thank you,
Alex
Hi @CheenuSing and @v-shex-msft,
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.
Any help would be greatly appreciated!
All the best,
Alex
Hi @aganes ,
I'd like to suggest you to do unpivot column on your time fields to convert records.
Unpivot columns (Power Query)
Then you can write formula to calculate based on unpivoted attribute and values.
Regards,
Xiaoxin Sheng
HI @aganes ,
I'd like some some sample data for test.
How to Get Your Question Answered Quickly
In addition, you can also take a look at following link about analytics across multiple date fields, but it seems not works on direct query mode and will generate huge amount of records.
Spread revenue across period based on start and end date, slice and dase this using different dates
Regards,
Xiaoxin Sheng
Hi @aganes ,
How do you filter the period is it through a slicer.
Can you upload sampledata, pbix and output expected to Google/One Drive and share the link here to find a solution.
Cheers
CheenuSing
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |