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
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.

Highlighted
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

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 155 members 2,091 guests
Recent signins: