Calculating Average Occupancy Between Times

[datbrink]

Hello PBI Professionals, 

I am attempting to create a stacked column chart where I will display the average meeting room utilization every 15 minutes. I have a "Start Time" and an "End Time" variable as is seen below. I will be using the "Type" column in the legend. If a room is being used from 12:15:00 PM to 12:45:00 PM then this room would apply for the 12:15:00,12:30:00, and 12:45:00 values. 

Start Time	End Time	Qty	Type
9:27:00 AM	3:17:00 PM	1	Student
8:15:00 AM	2:14:00 PM	1	Professional
7:23:00 AM	4:15:00 PM	1	Professional
2:30:00 AM	3:15:00 PM	1	Professional
12:15:00 PM	12:45:00 PM	1	Student

I have already created a separate time table that holds a 15 minute incremental count from 00:00:00 to 23:45:00. This has a column titled 'time15'. 

How might I go about creating the average count by hour? I created a model that shows me the exact utilization by 15 minute increment by day, but I want to create a model that only shows the average per 15 minute increment regardless of day. In my exact model I used the "Qty" column with a COUNTA() function. The following image is an example of what I created with an exact count. Ideally, the chart with the average would have a max value of 45 on the y-axis rather than 20,000 as is seen in the exact count. 

Unfortunately I am limited on how much data I can share due to confidentiality. 

My original DAX code for the measure used to obtain the exact count is: 

Measure = CALCULATE(COUNTA(data[Qty]),FILTER(data,data[Start Time] <= SELECTEDVALUE('timetable'[time15]) && SELECTEDVALUE('timetable'[time15]) <= data[End Time]))

I would appreciate any help that can be provided. I cannot seem to get past this, and I feel like I have tried everything. 

Thank you in advance. 

[AlexisOlson]

If you want an average per room per day, then you need to divide by the number of rooms and the number of days selected.

[datbrink]

Alexis, 

Thank you for your response. The "Qty" column only has a value of 1 per room per day, which work has worked well when using a COUNTA function. Everytime that I think I have solved the average, it displays the "Student" and "Professional" values from the "Type" column as 1s for hte entire chart, meaning it is now flat and shows no trends regarding utilization by hour. 

Any ideas on how I can move beyond this? 

[AlexisOlson]

Can you share a sample pbix (with any sensitive information scrubbed)? The one set of data rows you provided isn't quite enough to work with.

[datbrink]

Alexis, 

Here is a link to the file. Please let me know if you have any questions or concerns. 

https://drive.google.com/file/d/1gvQvYLKHba0dMHh5oQy-sN-he9-A6iIj/view?usp=sharing

[v-chenwuz-msft · Accepted solution]

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.

[AlexisOlson]

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.

[datbrink]

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. 

[AlexisOlson]

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.