1

I am trying to create a "meetingroom occupancy" matrix in Power BI. The raw data contains bookings per day per Room. The maximum daily available time per room is 12 hours. I have created a Date Dimension Table for the dates.

I have tried to change datatypes, added the available time column in the query editor, added the available time as DAX column and as calculated measure, but all with no success. I have changed the available time for Room B to 1, and the result of the Subtotal was 13, so it looks like subtotals is only summing unique values, but I do not know how to solve this.

Could someone please explain to me what is happening and how I could solve this?

The input data is as follows:

Schedule_data

And my Date_Dimension is as follows:

Date Dimension

This is the current and desired result:

enter image description here

Bende
  • 91
  • 8
  • Not sure I understand. Why would you expect the available time to be doubled for two rooms? Or tripled for three rooms, etc.? – Jos Woolley Oct 22 '21 at 20:12
  • So the available time subtotal in the matrix should be equal to n x (unique number of rooms) for a given date)? – Jos Woolley Oct 22 '21 at 20:16
  • Hi Jos, that is correct. 2 rooms with 12 hours per day equals 24 hours available hours per day. Just out of curiosity, why would you not want to multiply by x rooms? Maybe I could use both numbers – Bende Oct 24 '21 at 13:40
  • 1
    Perhaps you should try using a measure in the matrix in place of the available time column, e.g. `AvailableTime = MAX(Dates[available time])*IF(HASONEVALUE(Schedule_data[Room]),1,DISTINCTCOUNT(Schedule_data[Room]))` – Jos Woolley Oct 24 '21 at 14:47
  • Thank you very much. For the example that I presented, it worked. The only problem was, that it does not work for multiple days, so I changed MAX to SUM and now it does what I want. – Bende Oct 25 '21 at 08:07
  • Ok, glad to know you've got it working! – Jos Woolley Oct 25 '21 at 08:15

0 Answers0