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" vari...
  • 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.

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.

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        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.