cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaulHallam Regular Visitor
Regular Visitor

Calculating Max and Averages across tables

Hi,

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

'DATA TABLE'

Annotation 2019-07-17 141956.jpg

&

'SENSOR ASSOCIATIONS TABLE'

Annotation 2019-07-17 141851.jpg
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
Super User

Re: Calculating Max and Averages across tables

@PaulHallam Please share data in excel using onedrive/google drive to get your answer quickly. Remove any sensitive information before sharing.






Did I answer your question? Mark my post as a solution.

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





PaulHallam Regular Visitor
Regular Visitor

Re: Calculating Max and Averages across tables

Please see data table Below

DATA TABLE

Sensor NumberDateTimeOccupied
Sensor 101/01/201909:001
Sensor 101/01/201910:000
Sensor 101/01/201911:001
Sensor 101/01/201912:000
Sensor 101/01/201913:001
Sensor 101/01/201914:000
Sensor 101/01/201915:000
Sensor 101/01/201916:000
Sensor 201/01/201909:000
Sensor 201/01/201910:000
Sensor 201/01/201911:000
Sensor 201/01/201912:000
Sensor 201/01/201913:000
Sensor 201/01/201914:000
Sensor 201/01/201915:001
Sensor 201/01/201916:001
Sensor 301/01/201909:000
Sensor 301/01/201910:000
Sensor 301/01/201911:000
Sensor 301/01/201912:000
Sensor 301/01/201913:000
Sensor 301/01/201914:000
Sensor 301/01/201915:000
Sensor 301/01/201916:000
Sensor 401/01/201909:000
Sensor 401/01/201910:000
Sensor 401/01/201911:000
Sensor 401/01/201912:001
Sensor 401/01/201913:001
Sensor 401/01/201914:001
Sensor 401/01/201915:001
Sensor 401/01/201916:001
Sensor 501/01/201909:001
Sensor 501/01/201910:001
Sensor 501/01/201911:001
Sensor 501/01/201912:001
Sensor 501/01/201913:001
Sensor 501/01/201914:001
Sensor 501/01/201915:001
Sensor 501/01/201916:001
Sensor 102/01/201909:001
Sensor 102/01/201910:000
Sensor 102/01/201911:001
Sensor 102/01/201912:000
Sensor 102/01/201913:001
Sensor 102/01/201914:000
Sensor 102/01/201915:000
Sensor 102/01/201916:000
Sensor 202/01/201909:001
Sensor 202/01/201910:000
Sensor 202/01/201911:000
Sensor 202/01/201912:000
Sensor 202/01/201913:000
Sensor 202/01/201914:000
Sensor 202/01/201915:001
Sensor 202/01/201916:001
Sensor 302/01/201909:001
Sensor 302/01/201910:001
Sensor 302/01/201911:001
Sensor 302/01/201912:000
Sensor 302/01/201913:001
Sensor 302/01/201914:001
Sensor 302/01/201915:000
Sensor 302/01/201916:000
Sensor 402/01/201909:001
Sensor 402/01/201910:001
Sensor 402/01/201911:001
Sensor 402/01/201912:001
Sensor 402/01/201913:001
Sensor 402/01/201914:001
Sensor 402/01/201915:001
Sensor 402/01/201916:000
Sensor 502/01/201909:001
Sensor 502/01/201910:001
Sensor 502/01/201911:000
Sensor 502/01/201912:000
Sensor 502/01/201913:000
Sensor 502/01/201914:000
Sensor 502/01/201915:000
Sensor 502/01/201916:001

 

Thanks

P

PaulHallam Regular Visitor
Regular Visitor

Re: Calculating Max and Averages across tables

DATA TABLE

Sensor NumberDateTimeOccupied
Sensor 101/01/201909:001
Sensor 101/01/201910:000
Sensor 101/01/201911:001
Sensor 101/01/201912:000
Sensor 101/01/201913:001
Sensor 101/01/201914:000
Sensor 101/01/201915:000
Sensor 101/01/201916:000
Sensor 201/01/201909:000
Sensor 201/01/201910:000
Sensor 201/01/201911:000
Sensor 201/01/201912:000
Sensor 201/01/201913:000
Sensor 201/01/201914:000
Sensor 201/01/201915:001
Sensor 201/01/201916:001
Sensor 301/01/201909:000
Sensor 301/01/201910:000
Sensor 301/01/201911:000
Sensor 301/01/201912:000
Sensor 301/01/201913:000
Sensor 301/01/201914:000
Sensor 301/01/201915:000
Sensor 301/01/201916:000
Sensor 401/01/201909:000
Sensor 401/01/201910:000
Sensor 401/01/201911:000
Sensor 401/01/201912:001
Sensor 401/01/201913:001
Sensor 401/01/201914:001
Sensor 401/01/201915:001
Sensor 401/01/201916:001
Sensor 501/01/201909:001
Sensor 501/01/201910:001
Sensor 501/01/201911:001
Sensor 501/01/201912:001
Sensor 501/01/201913:001
Sensor 501/01/201914:001
Sensor 501/01/201915:001
Sensor 501/01/201916:001
Sensor 102/01/201909:001
Sensor 102/01/201910:000
Sensor 102/01/201911:001
Sensor 102/01/201912:000
Sensor 102/01/201913:001
Sensor 102/01/201914:000
Sensor 102/01/201915:000
Sensor 102/01/201916:000
Sensor 202/01/201909:001
Sensor 202/01/201910:000
Sensor 202/01/201911:000
Sensor 202/01/201912:000
Sensor 202/01/201913:000
Sensor 202/01/201914:000
Sensor 202/01/201915:001
Sensor 202/01/201916:001
Sensor 302/01/201909:001
Sensor 302/01/201910:001
Sensor 302/01/201911:001
Sensor 302/01/201912:000
Sensor 302/01/201913:001
Sensor 302/01/201914:001
Sensor 302/01/201915:000
Sensor 302/01/201916:000
Sensor 402/01/201909:001
Sensor 402/01/201910:001
Sensor 402/01/201911:001
Sensor 402/01/201912:001
Sensor 402/01/201913:001
Sensor 402/01/201914:001
Sensor 402/01/201915:001
Sensor 402/01/201916:000
Sensor 502/01/201909:001
Sensor 502/01/201910:001
Sensor 502/01/201911:000
Sensor 502/01/201912:000
Sensor 502/01/201913:000
Sensor 502/01/201914:000
Sensor 502/01/201915:000
Sensor 502/01/201916:001
PaulHallam Regular Visitor
Regular Visitor

Re: Calculating Max and Averages across tables

Super User
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?






Did I answer your question? Mark my post as a solution.

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





PaulHallam Regular Visitor
Regular Visitor

Re: Calculating Max and Averages across tables

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

Super User
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)






Did I answer your question? Mark my post as a solution.

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





Highlighted
PaulHallam Regular Visitor
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
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.

 






Did I answer your question? Mark my post as a solution.

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





Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 433 members 4,215 guests
Please welcome our newest community members: