Room Utilization Report - Feedback Request

DAX DAX Calculations
1

Hello,

I am creating a report to analyze the use of rooms in our college. We have a mix of conference and seminar rooms, and Lecture halls. After researching various forums, I have come up with the attached Power Bi report. I am not sure if I have done this correctly and if the results I am getting are right. Also, my total duration hrs. and average hours compute the same. So I am sure I have made a mistake. But not sure what it is.

I also have a DAX for Occupied days in a month by room, please review if this is done correctly. I am not an expert yet at DAX and Power BI. Can you all please review and provide me with your feedback? I would be grateful for all/any feedback. Thank you in advance for your time.

Kind Regards
2023_04_Room_Utilization-CHECK-2.pbix (415.2 KB)

1 Like
2

Bumping this post for more visibility from our experts and users.

3

Goodevening @hadeeh

I would like to give some comments and my additional approach of this, in my opinion, important reporting.

Workout attached:
2023_04_Room_Utilization-CHECK-2 (v2).pbix (420.7 KB)

  1. Added a measure for the calculation of the day capacity of the rooms

    Capacity (working days x rooms) = // days, excluding given holidays, excluding weekends 
CALCULATE( COUNTA('Calendar'[Date]) * DISTINCTCOUNT( RRS_Data[Rooms]) ,  
                FILTER( 'Calendar', and(WEEKDAY( 'Calendar'[Date] ) < 6 , 'Calendar'[Date] <> RELATED( Holidays[Dates] )) ))

  2. Added a measure for the hour- capacity of the rooms, multiplying the day capacity with 7, which aligns with your calculated column “Work hours actual” (only on daily basis in Calendar) .

  3. Adjusted the Frequency calculation, which in my opinion should be based upon Days instead of hours, as the Booking Count is based upon days: each line in the RRS-Data-table has a different day-start date per room, with an actual daily booking, the total count of the RRS Data table are the actual days booked as far as I could analyse.

    DS Frequency = DIVIDE([Booking Count], [Capacity (working days x rooms)])

  4. Used just a different formula, with exactly the same outcome for Utilization:

    DS Utilization = DIVIDE (  SUM (RRS_Data[DurationInHrs] ), [Capacity (norm hours x rooms )],  0  )

  5. The reason why the Total of both Frequency and Utilization is not correct is that the denominator for the total, the calculated column “Work hours actual”, does not add up correctly in the context given.
    The Total for the denominator for both DS Frequency and DS Utilization is adding up as might be expected, and therefore counts correctly.

PS

  1. The daily capacity calculation can be checked in detail at page " Step 1 Capacity in days ", filter just on one room.

  2. The full capacity of all rooms is not calculated in this report, the Total Utilization is OVERSTATED, as the capacity is based upon the booked rooms, ( * DISTINCTCOUNT( RRS_Data[Rooms])), non-booked rooms capacity is missing. It is therefore better to calculate the capacity from a separate Lookup-table, listing all the rooms.

Some summary comparison within the table below:

image
image992Ă—468 41.4 KB

I hope it helps you further,
kind regards, Delta Select

2 Likes
4

Dear @deltaselect,

Thank you for your valuable comments. I have downloaded the file and going to review it and your comments and get back.

Best Regards,
Hazra

5

Your feedback has given me much clarity on where I went wrong. Thanks a million, and much appreciate your time and valuable feedback. Best Regards