Forum Discussion
Calculating Average Occupancy Between Times
Hi datbrink ,
per room per day.
I think you are only counting the number of occurrences of this room within these dates. If you want to calculate the average, how many days are there within these dates.
Total number of occurrences / total number of days
Measure :
Room_Counter = DIVIDE( COUNTROWS( FILTER( RawData, RawData[Start Time] <= SELECTEDVALUE( 'DateTime Table'[Datetime] ) && SELECTEDVALUE( 'DateTime Table'[Datetime] ) <= RawData[Stop Time] ) ), COUNTROWS( VALUES( RawData[Start Date] ) ) )Pbix file in the end.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hmm. There are over 100 rows for 9/9/2021 that include 9 AM. Since there is no room number assigned to any rows, I can't tell if you have lots of people in a few rooms or you have over 100 rooms. It's not possible to compute utilization percent without knowing how many rooms are involved since that number needs to be in the denominator.
For clarification purposes, I have multiple locations (more than 20) with their own set of rooms that are being analyzed, but I have scrubbed that data for the sake of confidentiality. Would there be a way to set the denominator equal to a measure that changes the count of active rooms based on how I may be filtering?
For example, if we are looking at the example file I gave you, could we create a measure that calculates the AVERAGE(COUNTA(Qty) by day by binned hour to tell us how many rooms, on the average day, are being utilized in the specified 15 minute bin?
Sorry this is so convoluted, I am very new to this program and only have prior experience with R.
If each row had the proper identifiers, you can divide by a DISTINCTCOUNT of that ID, which can be filtered (or unfiltered) according to your preference.
In summary, yes. If you have the relevant fields, then it's certainly possible to include them in the denominator.