Bring your healthcare data to life with Power BI! In this step-by-step tutorial by DataScientist.ca, learn how to design a complete Health Care Analysis Dashboard from data import to visualization. Track hospital performance, patient insights, and provider efficiency with interactive dashboards and key DAX measures.
Case study overview: measuring hospital, patient and provider performance
This Power BI project builds a complete healthcare analysis dashboard, from data import to visualization, to track hospital performance, patient insights and provider efficiency across the US health industry.
The business problem. Healthcare organizations collect large amounts of data on hospitals, patients and providers, but leaders rarely see it in one place. Without a shared view, it is hard to tell which hospitals are performing well, which patient groups drive cost and activity, and which providers work efficiently.
The solution. Import and clean the data, shape it into a clear model, and write DAX measures that feed three views of the business: hospital performance, patient insights and provider efficiency. Interactive slicers let leaders move from the national picture to a single state, hospital or provider.
Who it is for. Healthcare analysts, hospital and health system managers, Power BI developers, and learners who want an end-to-end healthcare analytics project.
The data: US healthcare records
The project uses a US healthcare dataset covering hospitals, patients and providers. A typical layout includes hospital or facility name, state, provider or physician, specialty, patient age and gender, diagnosis or condition, visit or admission date, length of stay, charges or cost, and an outcome or satisfaction field. [Adjust this list to the fields in your files, and state the source and number of records.]
Data quality issues to handle
- State, hospital and provider names appear in inconsistent forms and need standardizing.
- Dates arrive as text and must be converted before time analysis.
- Missing values in cost or stay fields need a stated rule, such as excluding or labelling them.
- Totals from different source tables must reconcile before they are combined.
A published tutorial should use public, synthetic or anonymized data, and the report should not expose patient identifiers.
Import, cleaning and data model
Import and clean in Power Query
- Import each file or table, and set the correct data types.
- Trim and standardize text such as state, hospital and specialty names.
- Handle missing values, and remove duplicate rows.
- Add helper columns, such as age group and length of stay.
- Check row counts and totals against the source before loading.
Data model
A star schema keeps filtering predictable and the report fast.
| Table | Type | Typical contents |
|---|---|---|
| Visits | Fact | One row per visit or admission, with keys, dates, stay length, charges |
| Hospital | Dimension | Hospital name, state, type |
| Provider | Dimension | Provider name, specialty |
| Patient | Dimension | Age group, gender |
| Date | Dimension | Date, month, quarter, year, marked as the date table |
Relate each dimension to the fact table, one-to-many, hide the keys, and report from measures.
Key DAX measures
Table and column names are examples, so match them to your model.
Hospital performance
Total Visits = COUNTROWS ( Visits )
Avg Length of Stay = AVERAGE ( Visits[LengthOfStay] )
Total Charges = SUM ( Visits[Charges] )
Avg Charge per Visit = DIVIDE ( [Total Charges], [Total Visits] )Patient insights
Total Patients = DISTINCTCOUNT ( Visits[PatientID] )
Visits per Patient = DIVIDE ( [Total Visits], [Total Patients] )
Share of Visits % =
DIVIDE ( [Total Visits], CALCULATE ( [Total Visits], ALLSELECTED () ) )Provider efficiency
Visits per Provider =
DIVIDE ( [Total Visits], DISTINCTCOUNT ( Visits[ProviderKey] ) )
Charge per Day of Stay =
DIVIDE ( [Total Charges], SUM ( Visits[LengthOfStay] ) )
Visits Prior Year =
CALCULATE ( [Total Visits], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Visits YoY % =
DIVIDE ( [Total Visits] - [Visits Prior Year], [Visits Prior Year] )Efficiency measures need context. A provider with high visits per provider may see simpler cases, so compare providers within the same specialty and consider case mix before drawing conclusions.
Dashboard walkthrough
| View | Question answered | Main visuals |
|---|---|---|
| Hospital performance | Which hospitals and states lead or lag? | KPI cards, map of visits or charges by state, ranked hospitals, average stay by hospital |
| Patient insights | Who are the patients and what drives activity? | Age group and gender breakdown, top diagnoses, visits trend over time |
| Provider efficiency | How productive are providers? | Visits per provider by specialty, charge per day of stay, ranked provider table |
Design choices that help readers
- KPI cards sit at the top of every view, so headline numbers are always visible.
- Slicers for state, hospital, specialty and date are synced across views.
- Ranked bars and a map pair totals with geography.
- Conditional formatting flags values far above or below the average.
- Tooltips show visit counts, so small groups are not over-read.
Explore the working report in the live dashboard.
Insights, business value and lessons learned
What the dashboard reveals
- [Hospitals: name the top and bottom hospital or state by visits, and by average stay.]
- [Patients: describe the age group and gender with the most visits, and the top diagnosis.]
- [Providers: name the specialty with the highest visits per provider.]
- [Cost: give total charges and average charge per visit.]
- [Trend: describe the year-over-year change in visits.]
Replace each prompt with the figure shown in your live dashboard.
Business value
- Leaders get one view of hospital, patient and provider peCase study overview: measuring hospital, patient and provider performance
This Power BI project builds a complete healthcare analysis dashboard, from data import to visualization, to track hospital performance, patient insights and provider efficiency across the US health industry.
The business problem. Healthcare organizations collect large amounts of data on hospitals, patients and providers, but leaders rarely see it in one place. Without a shared view, it is hard to tell which hospitals are performing well, which patient groups drive cost and activity, and which providers work efficiently.
The solution. Import and clean the data, shape it into a clear model, and write DAX measures that feed three views of the business: hospital performance, patient insights and provider efficiency. Interactive slicers let leaders move from the national picture to a single state, hospital or provider.
Who it is for. Healthcare analysts, hospital and health system managers, Power BI developers, and learners who want an end-to-end healthcare analytics project.The data: US healthcare records
The project uses a US healthcare dataset covering hospitals, patients and providers. A typical layout includes hospital or facility name, state, provider or physician, specialty, patient age and gender, diagnosis or condition, visit or admission date, length of stay, charges or cost, and an outcome or satisfaction field. [Adjust this list to the fields in your files, and state the source and number of records.]
Data quality issues to handle- State, hospital and provider names appear in inconsistent forms and need standardizing.
- Dates arrive as text and must be converted before time analysis.
- Missing values in cost or stay fields need a stated rule, such as excluding or labelling them.
- Totals from different source tables must reconcile before they are combined.
A published tutorial should use public, synthetic or anonymized data, and the report should not expose patient identifiers.Import, cleaning and data model
Import and clean in Power Query- Import each file or table, and set the correct data types.
- Trim and standardize text such as state, hospital and specialty names.
- Handle missing values, and remove duplicate rows.
- Add helper columns, such as age group and length of stay.
- Check row counts and totals against the source before loading.
Data model
A star schema keeps filtering predictable and the report fast.TableTypeTypical contentsVisitsFactOne row per visit or admission, with keys, dates, stay length, chargesHospitalDimensionHospital name, state, typeProviderDimensionProvider name, specialtyPatientDimensionAge group, genderDateDimensionDate, month, quarter, year, marked as the date table
Relate each dimension to the fact table, one-to-many, hide the keys, and report from measures.Key DAX measures
Table and column names are examples, so match them to your model.
Hospital performanceTotal Visits = COUNTROWS ( Visits ) Avg Length of Stay = AVERAGE ( Visits[LengthOfStay] ) Total Charges = SUM ( Visits[Charges] ) Avg Charge per Visit = DIVIDE ( [Total Charges], [Total Visits] )
Patient insightsTotal Patients = DISTINCTCOUNT ( Visits[PatientID] ) Visits per Patient = DIVIDE ( [Total Visits], [Total Patients] ) Share of Visits % = DIVIDE ( [Total Visits], CALCULATE ( [Total Visits], ALLSELECTED () ) )
Provider efficiencyVisits per Provider = DIVIDE ( [Total Visits], DISTINCTCOUNT ( Visits[ProviderKey] ) ) Charge per Day of Stay = DIVIDE ( [Total Charges], SUM ( Visits[LengthOfStay] ) ) Visits Prior Year = CALCULATE ( [Total Visits], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) Visits YoY % = DIVIDE ( [Total Visits] - [Visits Prior Year], [Visits Prior Year] )
Efficiency measures need context. A provider with high visits per provider may see simpler cases, so compare providers within the same specialty and consider case mix before drawing conclusions.Dashboard walkthroughViewQuestion answeredMain visualsHospital performanceWhich hospitals and states lead or lag?KPI cards, map of visits or charges by state, ranked hospitals, average stay by hospitalPatient insightsWho are the patients and what drives activity?Age group and gender breakdown, top diagnoses, visits trend over timeProvider efficiencyHow productive are providers?Visits per provider by specialty, charge per day of stay, ranked provider table
Design choices that help readers- KPI cards sit at the top of every view, so headline numbers are always visible.
- Slicers for state, hospital, specialty and date are synced across views.
- Ranked bars and a map pair totals with geography.
- Conditional formatting flags values far above or below the average.
- Tooltips show visit counts, so small groups are not over-read.
Explore the working report in the live dashboard.Insights, business value and lessons learned
What the dashboard reveals- [Hospitals: name the top and bottom hospital or state by visits, and by average stay.]
- [Patients: describe the age group and gender with the most visits, and the top diagnosis.]
- [Providers: name the specialty with the highest visits per provider.]
- [Cost: give total charges and average charge per visit.]
- [Trend: describe the year-over-year change in visits.]
Replace each prompt with the figure shown in your live dashboard.
Business value- Leaders get one view of hospital, patient and provider performance.
- Comrformance.
- Comparisons by state, hospital and specialty point to where to investigate.
- Shared measures keep every report consistent.
Lessons for Power BI builders
- Standardize names before modelling, or the same hospital will appear twice.
- Compare like with like, such as providers within the same specialty.
- Reconcile totals with the source before building visuals.
- Keep patient identifiers out of published reports.
Frequently asked questions
What is a healthcare analysis dashboard? It is an interactive report that summarizes hospital, patient and provider data, so managers can track performance, activity and cost.
How do I measure provider efficiency in Power BI? Create measures such as visits per provider and charge per day of stay, and compare providers within the same specialty.
What DAX measures should a healthcare dashboard include? Start with total visits, patients, average length of stay, total charges and average charge per visit, then add year-over-year change and share-of-total measures.
Why use a star schema for healthcare data? A star schema keeps one fact table of visits and separate dimensions for hospital, provider, patient and date, which keeps filtering predictable and reports fast.
How do I compare hospitals fairly? Compare hospitals of similar type and size, and consider patient mix, because raw totals favour larger facilities.
Try it yourself
Explore the live dashboard, then follow the step-by-step tutorial and use the complete data and Power BI files to build it yourself. Need a healthcare analytics dashboard for your organization? Contact datascientist.ca