Forum Discussion

datbrink's avatar
datbrink
New Member
4 years ago
Solved

Calculating Average Occupancy Between Times

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 TimeEnd TimeQtyType
9:27:00 AM3:17:00 PM1Student
8:15:00 AM2:14:00 PM1Professional
7:23:00 AM4:15:00 PM1Professional
2:30:00 AM3:15:00 PM1Professional
12:15:00 PM12:45:00 PM1Student

 

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. 

 

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

8 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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's avatar
      datbrink
      New Member
      4 years ago

      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's avatar
        AlexisOlson
        Super User
        4 years ago

        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.