Calculate Percentage

[Anonymous]

Hi All,

I'd love some help with calculating percentages for a booking system please. I want to calculate the percentage of appointments booked / room capacity, per room, per day, in order to show this as a visualisation.

I have 2 tables. One is a list of appointments. Each appointment has its own ID, date, time and assigned room.  The other table is a list of rooms, and total available appointments per day. The clinic data is not static. It often changes.

Example (Dummy data)

Table 1 - Clinic Data

Room	Total Appointments Per Day
1	23
2	25
3	25
4	31
5	23

Table 2 - Schedule

Room	Date	Time	Appointment ID
1	1 January	09:00	716181
1	1 January	09:15	716899
2	1 January	09:05	716435
3	2 January	17:00	716898

Thanks

[MFelix · Accepted solution]

That's @LittleBird.

Only based on the information you provide can you create the following measures:

Total appointments = SUM(Rooms[Total Appointments per day])

Ocupation = DISTINCTCOUNT(Schedule[Appointment ID])

Room availability = SUMX(Rooms,[Total appointments] - [Ocupation]) 

% availability = [Room availability] / [Total appointments]

Create a different measure to have the step layout now just use the ones you need in your visualization:

Note that your information is very small if you need other requirements, share a sample file and the expected result.

[Anonymous]

Thanks Miguel, this is just what I needed.

Happy new year.

[littlemojopuppy]

Where you're unclear is how long are appointments.  Are they a standard allotment of time or do they have individual start and end times?  Without knowing that this is impossible.