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.

v-chenwuz-msft's avatar
v-chenwuz-msft
Icon for Community Support rankCommunity Support
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.