Case study overview: one dashboard for hospital management reporting
This Power BI hospital analytics dashboard brings patient, admission and cost data into a multi-page report with executive KPIs and drill-down patient views, built for healthcare management reporting.
The business problem. Hospital data lives in separate tables for patients, admissions, conditions, doctors and billing. Executives want a quick read on volumes, stays and costs, while analysts need to drill into individual patient records. Spreadsheet reports serve neither group well, and they are slow to refresh.
The solution. A properly designed data model, a library of reusable DAX measures, and a report with two layers: a Patient Summary page for executive KPIs and trends, and a Patient Detail page for record-level review. Gradient formatting highlights where values are high or low at a glance.
Who it is for. Data analysts, Power BI developers, business intelligence professionals, hospital managers, and students learning professional dashboard design.
Understanding the dataset
The project uses a healthcare dataset with patient and admission records. A typical layout includes patient ID, age, gender, medical condition, admission and discharge dates, admission type, doctor, hospital, insurance provider, billing amount and test results. [Adjust this list to the columns in your files, and name the source and the number of records.]
Data quality issues to handle
- Dates arrive as text and must be converted before length of stay can be calculated.
- Names and categories may differ in capitalization or spelling.
- Duplicate records and negative or unusual billing values need checking.
- Free-text fields should be turned into consistent categories.
A public tutorial should use synthetic or fully anonymized data, and the published report should never expose real patient identifiers.
Designing a proper data model
The model follows a star schema: one fact table of admissions surrounded by dimension tables. This keeps filtering predictable and the report fast.
| Table | Type | Typical contents |
|---|---|---|
| Admissions | Fact | One row per admission, with keys, admission and discharge dates, billing amount |
| Patient | Dimension | Patient ID, age, age group, gender |
| Condition | Dimension | Medical condition, admission type |
| Doctor | Dimension | Doctor, hospital |
| Insurance | Dimension | Insurance provider |
| Date | Dimension | Date, month, quarter, year, marked as the date table |
Modelling steps
- Clean the data in Power Query and set correct data types.
- Create length of stay and age group columns.
- Split descriptive attributes into dimension tables with keys.
- Relate each dimension to the fact table, one-to-many, with single-direction filtering.
- Hide keys and raw numeric columns, so report authors use measures.
DAX measures and executive KPIs
The executive KPIs sit on top of a small set of reusable measures. Table and column names are examples, so match them to your model.
Total Patients = DISTINCTCOUNT ( Admissions[PatientID] )
Total Admissions = COUNTROWS ( Admissions )
Total Billing = SUM ( Admissions[BillingAmount] )
Avg Billing per Admission = DIVIDE ( [Total Billing], [Total Admissions] )
Avg Length of Stay =
AVERAGEX (
Admissions,
DATEDIFF ( Admissions[AdmissionDate], Admissions[DischargeDate], DAY )
)
Avg Patient Age = AVERAGE ( Patient[Age] )
Emergency Admissions % =
DIVIDE (
CALCULATE ( [Total Admissions], Admissions[AdmissionType] = "Emergency" ),
[Total Admissions]
)
Admissions Prior Year =
CALCULATE ( [Total Admissions], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Admissions YoY % =
DIVIDE ( [Total Admissions] - [Admissions Prior Year], [Admissions Prior Year] )Card visuals show patients, admissions, billing, average stay and average age. Each uses a measure, so the numbers respond to every slicer. Ensure discharge dates are never earlier than admission dates, or the stay measure will return negative values.
Dashboard walkthrough: Patient Summary and Patient Detail
The report has two layers, so executives and analysts each get the view they need.
| Page | Audience | Main visuals |
|---|---|---|
| Patient Summary | Executives | KPI cards, admissions by month, billing by condition, admission type split, age group and gender breakdown |
| Patient Detail | Analysts and clinical managers | Table of admissions with condition, doctor, stay length and billing, filtered by slicers |
Gradient formatting. Conditional formatting colours values on a scale, so high and low results stand out without reading every number. Apply it to:
- Matrix cells, such as billing by condition and age group
- Table columns, such as length of stay and billing amount
- Bar charts, where colour follows the value
Use one colour scale consistently, with accessible contrast, and avoid red and green alone.
Navigation and layout
- Slicers for date, condition, admission type and hospital are synced across pages.
- KPI cards sit at the top, with detail below, so the page reads from summary to specifics.
- Drill-through from a chart opens the Patient Detail page for the selected group.
- A consistent theme, spacing and titles make the report feel professional.
Explore the working report in the live dashboard.
Insights, business value and lessons learned
What the dashboard reveals
- [Volume: give total patients, admissions and billing for your selected period.]
- [Conditions: name the condition with the most admissions and the one with the highest average billing.]
- [Stay: give the average length of stay and the condition or admission type with the longest stays.]
- [Demographics: describe the age group and gender with the most admissions.]
- [Trend: describe how admissions changed by month or year.]
Replace each prompt with the figure shown in your live dashboard.
Business value
- Executives get a one-page read on volumes, stays and costs.
- Analysts can drill from a trend to the patient records behind it.
- Reusable measures keep numbers consistent across reports and meetings.
Lessons for Power BI builders
- Design the data model before any visuals, because every measure depends on it.
- Build measures once and reuse them across pages.
- Use gradient formatting sparingly and consistently, so colour carries meaning.
- Separate summary and detail views, so each audience is not overloaded.
- Test measures against a hand-checked sample before publishing.
Frequently asked questions
What is a hospital analytics dashboard? It is an interactive report that summarizes patient, admission and billing data, so managers can track volumes, length of stay and costs.
What KPIs should a hospital dashboard include? Common KPIs are patients, admissions, average length of stay, average billing, emergency admission share and year-over-year change in admissions.
How do I calculate length of stay in Power BI? Use DATEDIFF between the admission and discharge dates, in days, then average it with AVERAGEX.
What is gradient formatting in Power BI? Gradient formatting is conditional formatting that colours cells or bars along a scale, so high and low values are easy to spot.
Why use a star schema for healthcare data? A star schema keeps one fact table of events, such as admissions, and separate dimension tables, so filters work predictably and the report stays fast.
Try it yourself
Explore the live dashboard, download the complete project files, and follow the complete free Power BI course to build it yourself. Need a hospital or healthcare reporting dashboard for your organization? Contact datascientist.ca.