cancel
Showing results for
Did you mean:
Regular Visitor

## Calculating Max and Averages across tables

Hi,

I have the following data model (2 tables linked by Sensor Number)

'DATA TABLE'

&

'SENSOR ASSOCIATIONS TABLE'

I need to have 4 measures that can calculate;

1. Maximum number of collaborative rooms used in any hour period = (2)
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)
3. 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)
4. 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.

Thanks

16 REPLIES 16
Super User

## Re: Calculating Max and Averages across tables

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

Regular Visitor

## Re: Calculating Max and Averages across tables

DATA TABLE

 Sensor Number Date Time Occupied Sensor 1 01/01/2019 09:00 1 Sensor 1 01/01/2019 10:00 0 Sensor 1 01/01/2019 11:00 1 Sensor 1 01/01/2019 12:00 0 Sensor 1 01/01/2019 13:00 1 Sensor 1 01/01/2019 14:00 0 Sensor 1 01/01/2019 15:00 0 Sensor 1 01/01/2019 16:00 0 Sensor 2 01/01/2019 09:00 0 Sensor 2 01/01/2019 10:00 0 Sensor 2 01/01/2019 11:00 0 Sensor 2 01/01/2019 12:00 0 Sensor 2 01/01/2019 13:00 0 Sensor 2 01/01/2019 14:00 0 Sensor 2 01/01/2019 15:00 1 Sensor 2 01/01/2019 16:00 1 Sensor 3 01/01/2019 09:00 0 Sensor 3 01/01/2019 10:00 0 Sensor 3 01/01/2019 11:00 0 Sensor 3 01/01/2019 12:00 0 Sensor 3 01/01/2019 13:00 0 Sensor 3 01/01/2019 14:00 0 Sensor 3 01/01/2019 15:00 0 Sensor 3 01/01/2019 16:00 0 Sensor 4 01/01/2019 09:00 0 Sensor 4 01/01/2019 10:00 0 Sensor 4 01/01/2019 11:00 0 Sensor 4 01/01/2019 12:00 1 Sensor 4 01/01/2019 13:00 1 Sensor 4 01/01/2019 14:00 1 Sensor 4 01/01/2019 15:00 1 Sensor 4 01/01/2019 16:00 1 Sensor 5 01/01/2019 09:00 1 Sensor 5 01/01/2019 10:00 1 Sensor 5 01/01/2019 11:00 1 Sensor 5 01/01/2019 12:00 1 Sensor 5 01/01/2019 13:00 1 Sensor 5 01/01/2019 14:00 1 Sensor 5 01/01/2019 15:00 1 Sensor 5 01/01/2019 16:00 1 Sensor 1 02/01/2019 09:00 1 Sensor 1 02/01/2019 10:00 0 Sensor 1 02/01/2019 11:00 1 Sensor 1 02/01/2019 12:00 0 Sensor 1 02/01/2019 13:00 1 Sensor 1 02/01/2019 14:00 0 Sensor 1 02/01/2019 15:00 0 Sensor 1 02/01/2019 16:00 0 Sensor 2 02/01/2019 09:00 1 Sensor 2 02/01/2019 10:00 0 Sensor 2 02/01/2019 11:00 0 Sensor 2 02/01/2019 12:00 0 Sensor 2 02/01/2019 13:00 0 Sensor 2 02/01/2019 14:00 0 Sensor 2 02/01/2019 15:00 1 Sensor 2 02/01/2019 16:00 1 Sensor 3 02/01/2019 09:00 1 Sensor 3 02/01/2019 10:00 1 Sensor 3 02/01/2019 11:00 1 Sensor 3 02/01/2019 12:00 0 Sensor 3 02/01/2019 13:00 1 Sensor 3 02/01/2019 14:00 1 Sensor 3 02/01/2019 15:00 0 Sensor 3 02/01/2019 16:00 0 Sensor 4 02/01/2019 09:00 1 Sensor 4 02/01/2019 10:00 1 Sensor 4 02/01/2019 11:00 1 Sensor 4 02/01/2019 12:00 1 Sensor 4 02/01/2019 13:00 1 Sensor 4 02/01/2019 14:00 1 Sensor 4 02/01/2019 15:00 1 Sensor 4 02/01/2019 16:00 0 Sensor 5 02/01/2019 09:00 1 Sensor 5 02/01/2019 10:00 1 Sensor 5 02/01/2019 11:00 0 Sensor 5 02/01/2019 12:00 0 Sensor 5 02/01/2019 13:00 0 Sensor 5 02/01/2019 14:00 0 Sensor 5 02/01/2019 15:00 0 Sensor 5 02/01/2019 16:00 1

Thanks

P

Regular Visitor

## Re: Calculating Max and Averages across tables

DATA TABLE

 Sensor Number Date Time Occupied Sensor 1 01/01/2019 09:00 1 Sensor 1 01/01/2019 10:00 0 Sensor 1 01/01/2019 11:00 1 Sensor 1 01/01/2019 12:00 0 Sensor 1 01/01/2019 13:00 1 Sensor 1 01/01/2019 14:00 0 Sensor 1 01/01/2019 15:00 0 Sensor 1 01/01/2019 16:00 0 Sensor 2 01/01/2019 09:00 0 Sensor 2 01/01/2019 10:00 0 Sensor 2 01/01/2019 11:00 0 Sensor 2 01/01/2019 12:00 0 Sensor 2 01/01/2019 13:00 0 Sensor 2 01/01/2019 14:00 0 Sensor 2 01/01/2019 15:00 1 Sensor 2 01/01/2019 16:00 1 Sensor 3 01/01/2019 09:00 0 Sensor 3 01/01/2019 10:00 0 Sensor 3 01/01/2019 11:00 0 Sensor 3 01/01/2019 12:00 0 Sensor 3 01/01/2019 13:00 0 Sensor 3 01/01/2019 14:00 0 Sensor 3 01/01/2019 15:00 0 Sensor 3 01/01/2019 16:00 0 Sensor 4 01/01/2019 09:00 0 Sensor 4 01/01/2019 10:00 0 Sensor 4 01/01/2019 11:00 0 Sensor 4 01/01/2019 12:00 1 Sensor 4 01/01/2019 13:00 1 Sensor 4 01/01/2019 14:00 1 Sensor 4 01/01/2019 15:00 1 Sensor 4 01/01/2019 16:00 1 Sensor 5 01/01/2019 09:00 1 Sensor 5 01/01/2019 10:00 1 Sensor 5 01/01/2019 11:00 1 Sensor 5 01/01/2019 12:00 1 Sensor 5 01/01/2019 13:00 1 Sensor 5 01/01/2019 14:00 1 Sensor 5 01/01/2019 15:00 1 Sensor 5 01/01/2019 16:00 1 Sensor 1 02/01/2019 09:00 1 Sensor 1 02/01/2019 10:00 0 Sensor 1 02/01/2019 11:00 1 Sensor 1 02/01/2019 12:00 0 Sensor 1 02/01/2019 13:00 1 Sensor 1 02/01/2019 14:00 0 Sensor 1 02/01/2019 15:00 0 Sensor 1 02/01/2019 16:00 0 Sensor 2 02/01/2019 09:00 1 Sensor 2 02/01/2019 10:00 0 Sensor 2 02/01/2019 11:00 0 Sensor 2 02/01/2019 12:00 0 Sensor 2 02/01/2019 13:00 0 Sensor 2 02/01/2019 14:00 0 Sensor 2 02/01/2019 15:00 1 Sensor 2 02/01/2019 16:00 1 Sensor 3 02/01/2019 09:00 1 Sensor 3 02/01/2019 10:00 1 Sensor 3 02/01/2019 11:00 1 Sensor 3 02/01/2019 12:00 0 Sensor 3 02/01/2019 13:00 1 Sensor 3 02/01/2019 14:00 1 Sensor 3 02/01/2019 15:00 0 Sensor 3 02/01/2019 16:00 0 Sensor 4 02/01/2019 09:00 1 Sensor 4 02/01/2019 10:00 1 Sensor 4 02/01/2019 11:00 1 Sensor 4 02/01/2019 12:00 1 Sensor 4 02/01/2019 13:00 1 Sensor 4 02/01/2019 14:00 1 Sensor 4 02/01/2019 15:00 1 Sensor 4 02/01/2019 16:00 0 Sensor 5 02/01/2019 09:00 1 Sensor 5 02/01/2019 10:00 1 Sensor 5 02/01/2019 11:00 0 Sensor 5 02/01/2019 12:00 0 Sensor 5 02/01/2019 13:00 0 Sensor 5 02/01/2019 14:00 0 Sensor 5 02/01/2019 15:00 0 Sensor 5 02/01/2019 16:00 1
Highlighted
Regular Visitor

Super User

## Re: Calculating Max and Averages across tables

@PaulHallam the result you provided in the original post, are we expecing the same result from the dataset you provided?

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

Regular Visitor

## Re: Calculating Max and Averages across tables

Yes, its the same sample data. Thanks for looking at this

Super User

## Re: Calculating Max and Averages across tables

@PaulHallam how you get to following number what is the logic?

Maximum number of collaborative rooms used in any hour period = (2)

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

Regular Visitor

## Re: Calculating Max and Averages across tables

Hi,

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.

Hope this makes it clear.

Super User

## Re: Calculating Max and Averages across tables

@PaulHallam hmmm i thought the same too but in this case we are getting room count 3, are you ignoring room with "-" dash.

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 25 members 847 guests
Recent signins: