Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi PBI Community,
I'm trying to calculate hourly % utilization with a sample of the following data points. I want to display a matrix visual that shows by day and hour, the percentage of rooms occupied (similar to the last image). I found this DAX on this site but couldn't get it to calculate correctly. I also need to ensure that the appropriate utilization is calculated for the hours inbetween "In" and "Out." For instance, if 'In' is 8am and 'Out' is 10am, I need to make sure that the DAX accounts for the 9am hour.
Sorry -- new to DAX and looking to learn from the community.
_______________________
Measure Utilization =
var _occupied =
CALCULATE(COUNT('Source'[Guest]),
FILTER(ALLSELECTED('Source'),
'Source'[In]<=MAX('Table Time'[Time])&&'Source'[Out]>=MAX('Table Time'[Time])&&'Source'[Date]=MAX('Table Calendar'[Date])))
var _utilization =
DIVIDE(_occupied,20) // for 20 rooms
return
IF(_utilization = BLANK(),0,_utilization)
@seanguerrero , change var _occupied like this and try
var _occupied =
Sumx( addcolumns( summarize( 'Source', 'Source'[Guest], source[Date] ) , "_1", calculate(COUNT('Source'[Guest]),
FILTER(ALLSELECTED('Source'),
'Source'[In]<=MAX('Table Time'[Time])&&'Source'[Out]>=MAX('Table Time'[Time])&&'Source'[Date]=MAX('Table Calendar'[Date])))), [_1])
Thanks for the reply @amitchandak. I changed var _occupied per your recommendation. After charting Source[Date] and Table Time[Time], I'm getting zeros. Any help would be greatly appreciated! Thanks again!
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!