Forum Discussion
Calculate Percentage
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
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.
3 Replies
- MFelix
Super User
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.
- littlemojopuppy
Community Champion
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.
