Room Utilization by Day and Hour

[tsupani]

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

[v-robertq-msft · Accepted solution]

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:

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.

[v-robertq-msft]

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"

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

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

4. Create a Matrix and place columns like this:

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.

[tsupani]

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:

Thanks so much for your time and help!

Gig

[v-robertq-msft]

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

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

4. Change the measure format to “Percentage” and create a Matrix and place columns like this:

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.

[tsupani]

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

[amitchandak]

@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)))