I have the following data model (2 tables linked by Sensor Number)
'SENSOR ASSOCIATIONS TABLE'
I need to have 4 measures that can calculate;
Maximum number of collaborative rooms used in any hour period = (2)
Collaborative Room Utilisation (46.9%) calculated as total room uses (15) divided by total possible room uses - 2 Rooms x 2 Days x 8 Hours (32)
Collaborative Sensor Utilisation (33.3%) calculated as total no. sensor uses (16) divided by total possible sensor uses - 3 Sensors Rooms x 2 Days x 8 Hours (48)
Collaborative Sensor Utilisation when the room is used (64.0%) calculated as total no. sensor uses (16) divided by total possible sensor uses when room is used (25)
I have tried lots of ways of doing this but cant crack it. Once i have the logic straight i can append for other things. The data set is millions of sensors, hundreds of rooms over several years. The results are subject to slicers on the report pages.
Apologies, as this data set is small it may be a little less obvious than with a full set.
Looking at the Sensor Associations Table there are two collaborative rooms (Room A and Room B) The maximum number measure that im trying to get is the maximum number of rooms that were used in any hour period over the dates selected. A room is counted as used if any sensor shows occupancy.
Because Room A and Room B were both occupied in the same hour (at 9:00,11:00 and 13:00 on the 02/01/2019) the maximum concurrent use of rooms is 2.