cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Room Utilization by Day and Hour

Hello. I’m looking for some assistance to find a way to calculate utilization (%) by day and hour that can show in a matrix table. 🙂 The total number of rooms available may be needed to calculate the utilization. 

Sample Data Set:

Room

Meeting Day

Start Time

End Time

CA0008

W

11:00 AM

12:15 PM

CA0008

TR

9:35 AM

10:50 AM

CA0008

TR

11:00 AM

12:15 PM

CA0008

T

3:00 PM

4:50 PM

CA0008

R

4:15 PM

5:30 PM

CA0008

MW

3:00 PM

4:15 PM

CB0115

TR

11:00 AM

12:15 PM

CB0115

TR

1:00 PM

2:15 PM

CB0115

R

3:00 PM

5:50 PM

CB0220

TR

10:00 AM

10:50 AM

CB0220

TR

4:00 PM

5:30 PM

CB0220

TR

4:00 PM

5:30 PM

CB0220

MWF

8:00 AM

8:50 AM

CC0030

TR

1:00 PM

2:50 PM

CC0030

MW

11:00 AM

12:50 PM

CC0132

T

2:00 PM

4:50 PM

CD0133

TR

2:00 PM

3:15 PM

CD0133

MW

2:00 PM

3:15 PM

CD0133

M

9:00 AM

11:50 AM

 

Sample Matrix Table:

Day/Time

8 AM

9 AM

10 AM

11 AM

12 PM

1 PM

2 PM

3 PM

4 PM

M

 

 

 

 

 

 

 

 

 

T

 

 

 

 

 

 

 

 

 

W

 

 

 

 

 

 

 

 

 

T

 

 

 

 

 

 

 

 

 

F

 

 

 

 

 

 

 

 

 

 

Appreciate any guidance and assistance!

Gig

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi, @tsupani 

According to your expected result, I think what can be achieved in Power BI is the data part, this is the result I can achieve:

v-robertq-msft_0-1606890477922.png

 

What’s more, I don’t think the detailed data(room number and time period) can also be displayed in Power BI Matrix, I suggest you to accept this reply as a solution and open a new case to ask this question so that more exports can research whether this can be achieved in Power BI.

This is my test pbix file according to your expected result(My steps is similar to the steps I mentioned before):

https://qiuyunus-my.sharepoint.com/:u:/g/personal/xiaoxin_qiuyunus_onmicrosoft_com/EWGlCWdBiS1Nqvigi...

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Community Support
Community Support

Hi, @tsupani 

According to your expected result, I think what can be achieved in Power BI is the data part, this is the result I can achieve:

v-robertq-msft_0-1606890477922.png

 

What’s more, I don’t think the detailed data(room number and time period) can also be displayed in Power BI Matrix, I suggest you to accept this reply as a solution and open a new case to ask this question so that more exports can research whether this can be achieved in Power BI.

This is my test pbix file according to your expected result(My steps is similar to the steps I mentioned before):

https://qiuyunus-my.sharepoint.com/:u:/g/personal/xiaoxin_qiuyunus_onmicrosoft_com/EWGlCWdBiS1Nqvigi...

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Community Support
Community Support

Hi, @tsupani 

According to your description, I can understand your requirement clearly, and you can follow my steps and find out if it’s exactly what you want:

  1. Go to power query editor, open the advanced editor, and pause this code:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZJNDoMgEIWvYli7eMNIYtlpm+5IjGniwnj/axSo4KA2LGZCwsd788O6qucAoFetWnwQWaAZXDhqS6aZnNpaAX1mnx6WzQ7BmshfobqUDw7MFJguCJ2ROV783rbKWL4ibjnJCKcRRKZaT0llpVtmLsyMrHmE1sgyOMyKERVUJ5T4j1KFccvb5z679dLsCTDuOhNlJ8Zd119QxHrfmb7f2csjnMwOhuUYExPNakz8aLkcSo1tXw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Room = _t, #"Meeting Day" = _t, #"Start Time" = _t, #"End Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Time", type time}, {"End Time", type time}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Meeting Day", Splitter.SplitTextByRepeatedLengths(1), {"Meeting Day.1", "Meeting Day.2", "Meeting Day.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Room", type text}, {"Meeting Day.1", type text}, {"Meeting Day.2", type text}, {"Meeting Day.3", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type1", {"Meeting Day.1", "Meeting Day.2", "Meeting Day.3"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Meeting Day"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Room", "Meeting Day", "Start Time", "End Time"})
in
#"Reordered Columns"

  1. Create a calculated table and transform [Start Time] and [End Time] to time type:
Time =

var _hour=GENERATESERIES(8,16,1)

return SELECTCOLUMNS(_hour,"Start Time",TIME([Value],1,0),"End Time",TIME([Value]+1,0,0),"Time period",TIME([Value],1,0)&" - "&TIME([Value]+1,0,0))

v-robertq-msft_0-1606812756542.png

  1. Create a measure and change measure type to "Percentage":
utilization =
var _allrooms=
CALCULATE(DISTINCTCOUNT('Table'[Room]),ALL('Table'))
var _usedrooms=
CALCULATE(
    COUNT('Table'[Room]),
    FILTER(ALLSELECTED('Table'),
    [Start Time]<=MAX('Time'[Start Time])&&[End Time]>=MAX('Time'[End Time])&&[Meeting Day]=MAX('Table'[Meeting Day])))
var _utilization=
DIVIDE(_usedrooms,_allrooms)
return
IF(_utilization=BLANK(),0,_utilization)

  1. Create a Matrix and place columns like this:

v-robertq-msft_1-1606812756562.png

 

And you can get what you want.

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-robertq-msft. I appreciate your help. I still can't get the right matrix table as most classes are scheduled in different patterns (TR, MWF, MW, etc.). For the matrix table, I expect to see the scheduling pattern by day (M, T, W, R, and F) at each hour slot. 

Let me further clarify this in a little more detail. 

Sample Data:

CA0008, 8:00 AM - 8:50 AM, MWF

CB0220, 8:00 AM - 8:50 AM, MW

CC0030, 8:00 AM - 8:50 AM, MW

CA0008, 8:00 AM- 8:50 AM, TR

CB0220, 8:00 AM - 8:50 AM, T

Utilization = (Total Number of Rooms Occupied/Total Number of Rooms Available)*100

Note: Total Number of Rooms Available - 10

Expected Matrix Table:

Expected Matrix Table.JPG

Thanks so much for your time and help!

Gig

Community Support
Community Support

Hi, @tsupani 

  1. According to your expected result, you want to create a matrix to calculate utilization (%) by day and hour, you can follow my steps:
  2. Create a calculated column [Time], then change the [Time] column to time type:
Time =

var _hour=GENERATESERIES(8,16,1)

return SELECTCOLUMNS(_hour,"Time",TIME([Value],0,0))

v-robertq-msft_0-1606804155586.png

 

  1. Create a measure in the main table:
utilization =

var _allrooms=

CALCULATE(DISTINCTCOUNT('Table'[Room]),ALL('Table'))

var _usedrooms=

CALCULATE(

    COUNT('Table'[Room]),

    FILTER(ALLSELECTED('Table'),

    [Start Time]<=MAX('Time'[Time])&&[End Time]>=MAX('Time'[Time])&&[Meeting Day]=MAX('Table'[Meeting Day])))

var _utilization=

DIVIDE(_usedrooms,_allrooms)

return

IF(_utilization=BLANK(),0,_utilization)
  1. Change the measure format to “Percentage” and create a Matrix and place columns like this:

v-robertq-msft_1-1606804155599.png

 

And you can get what you want.

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for taking the time to find out the solution. 🙂

 

Sorry I wasn't clear about my communication on the first post. Your proposed solution seems to move in the right direction but still doesn't meet certain requirements. In particular, I hope to see the utilization by day (including M, T, W, R, and F) instead of the meeting day patterns (e.g., M, MW, MWF, etc.) and by the hour from 8 AM - 4 PM (e.g., 8:01 am - 9:00 am, 9:01 am - 10:00 am, etc.). 

Super User IV
Super User IV

@tsupani , first of all you need an hourly bucket . See if time table or a merge with time table can help

https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

 

then this measure should work

divide(distinctcount(Table[Room]), calculate(distinctcount(Table[Room]), allselected(Table)))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors