How to Build a Hospital Emergency Room Dashboard in Power BI

TL;DR

A hospital emergency room dashboard in Power BI can be built across four report pages, starting with a monthly view driven by a year and month slicer. The top KPI row tracks number of patients, average wait time, satisfaction score, and patients referred, while lower charts break the data down by admission status, age group, referral department, gender, race, and a day-by-hour heat map.

Transcript

hey guys welcome back to my channel data tutorials and in this video we are going to see a one more data analyst portfolio project and we are going to see this in a power V tool so in front of your screen you can see this will be an output or a final project and in screen also you can see the title of our dashboard is an Hospital emergency room das... Read More

Key Insights

• The hospital emergency room dashboard is a data analyst portfolio project that spans four separate dashboards, built start to end in Power BI and covering advanced DAX functions, advanced measures, and advanced calculated columns.
• The first dashboard page is a monthly view controlled by a slicer for year and month, so selecting January 2024, April 2024, or November 2023 refilters every KPI and chart on the page for that period.
• The four KPIs at the top of the monthly view are number of patients, average wait time, satisfaction score, and number of patients referred, giving a high level summary before any deeper breakdown.
• Patients referred means the patient arrived with an appointment already booked for a specific department such as oncology, orthopedics, or gynecology, rather than choosing a doctor on the spot at the hospital.
• The age group chart uses a 10 year interval, producing buckets such as 0 to 9 and 10 to 19, and the resulting distribution shows heavier patient volume in the early 30s through 50s and again in the 70 to 79 band.
• The 30 minute wait threshold is a target the presenter defined personally as the ideal patient wait time, and the chart splits patients into within target and missed target, with only 34 percent falling within target.
• Filtering to missed target shows 267 patients with an average wait time of 46.5, while the within target group averages 20 minutes, which the presenter describes as a good time.
• The day and hour analysis is a matrix chart or heat map with hours in rows and Monday through Sunday in columns, used to see when patient volume peaks so staffing and inventory resources can be planned.

Questions & Answers

Q: What is the Power BI hospital emergency room dashboard project about?

It is a data analyst portfolio project built end to end in Power BI that analyzes emergency room data for a hospital. The presenter places it in the hospital, emergency, medical, or pharmaceutical domain, noting that health is a fast growing industry where the data generated needs analysis so insights can optimize the industry's workflow. The project produces four separate dashboards, and the presenter walks through the design of each one, teaching advanced DAX functions, advanced measures, and advanced calculated columns along the way.

Q: How many dashboards are built in this Power BI project?

Four dashboards are designed in total. The video covers the design of each one in detail, which is why the presenter warns that it will be a longer video. The first dashboard is the monthly view, which contains the year and month slicer, four KPI cards, a daily trend graph, and a row of deeper analysis charts at the bottom. The presenter says viewers will come away with good designing skills and good developing skills by the end of the project.

Q: What KPIs are shown on the monthly view dashboard?

Four KPIs sit at the top of the monthly view and represent high level summary information for the selected period. They are the number of patients who visited in that month, the average wait time for the outpatient department, the satisfaction score collected as feedback from patients about their experience, and the number of patients referred. Because the page is a monthly view, every KPI recalculates for whichever year and month is chosen in the slicer.

Q: What does patients referred mean in the emergency room dashboard?

Referred means the patient arrived with an appointment already booked for a specific department, so they already know which doctor or specialty they are going to visit. Examples given include oncology, orthopedics, and gynecology. This contrasts with patients who go directly to the hospital and book an appointment on the spot without a prior referral. In the referral breakdown chart, none is the largest category, meaning most patients did not book a referred department in advance, while orthopedic, physiotherapy, and cardiology account for smaller shares.

Q: How is the patient wait time target defined in this dashboard?

The presenter set the target personally, declaring 30 minutes as the ideal patient wait time rather than taking it from the source data. The dashboard then calculates how many patients were seen within 30 minutes and how many were not, labeling them within target and missed target. Only 34 percent of patients fall within target, so most missed it. Filtering to missed target shows 267 patients with an average wait time of 46.5, while the within target group has an average wait time of 20 minutes.

Q: How are patient age groups grouped in the Power BI report?

The age groups use a 10 year interval, which the presenter chose because the interval size is up to the analyst. That produces buckets such as 0 to 9, 10 to 19, and so on in 10 year gaps. The chart then counts how many patients in each bucket visited the hospital. Reading the distribution, the presenter points out heavier patient volume in the early 30s through the 50s, and another concentration in the 70 to 79 range, which is the kind of insight the chart is meant to surface.

Q: Why does the dashboard include a day and hour heat map?

The day and hour view is built as a matrix chart or heat map with hours placed in the rows and the days Monday through Sunday in the columns, and the cells hold the number of patients. The presenter calls it an important analysis because seeing how many patients arrive in a specific hour on a specific day makes it possible to manage resources and manage inventory for the emergency room. Clicking a day such as Sunday also filters the rest of the dashboard to that day.

Q: How do the interactive filters on the dashboard work?

The dashboard is dynamic in two ways. The main slicer changes the year and month, so picking January 2024, April 2024, or November 2023 refilters the whole page. Beyond that, the charts themselves act as interactive or action filters: clicking missed target, within target, an age group such as 30 to 39 with 68 patients, a race such as African American, an admission status of admitted or not admitted, or a day such as Sunday filters all the other visuals to that selection.

Q: Where can the dataset and dashboard file for this project be downloaded?

The description provides the download links. The data is on Google Drive at a shared folder link, and the dashboard file is available through the presenter's topmate page at topmate.io/data_tutorials/1348434. The presenter also mentions a terminology document that explains each and every field in the dataset, so viewers can understand what the fields are and what they mean, and says the link will be added in the description box. A Part 2 video link is also listed in the description.

Summary & Key Takeaways

• The project builds a hospital emergency room dashboard in Power BI, positioned in the hospital, emergency, medical, or pharmaceutical domain. The presenter frames health as a fast growing industry where generated data needs analysis and insights so the workflow of the health industry can be optimized. Four dashboards are designed in total, each walked through step by step.

• The monthly view page opens with a year and month slicer and four KPIs: number of patients, average wait time, satisfaction score, and number of patients referred. A monthly view graph beneath the KPIs gives a daily, more granular picture, showing how many patients arrived on a given date such as February 5th and how each measure moves day by day.

• Lower charts deep dive into the data: patient admission status with admitted versus not admitted counts and percentages, patients by 10 year age group, patients by department referral, percentage seen within 30 minutes against target, patients by gender including a not confirming category, patients by race, and a day by hour heat map for resource planning.