HEALTHCARE ANALYTICS PROJECT
Healthcare Data Analytics
using Power BI
A comprehensive portfolio project demonstrating advanced healthcare analytics skills using Power BI, DAX, and data transformation techniques.
Project Overview: Emergency Department Analytics
This project showcases an end-to-end data analysis of hospital emergency department operations. It focuses on identifying opportunities to improve patient flow, optimize wait times, and enhance overall satisfaction scores.
By integrating data from various hospital systems, we created a star schema data model to enable sophisticated analysis of demographic correlations, department performance, and temporal patterns.
Key Focus Areas
- Patient flow analytics & Anomaly Detection
- Wait time optimization strategies
- Demographic analysis of satisfaction trends
- Interactive Dashboard for Executive Reporting
Data Architecture & Methodology
Data Acquisition & Prep
- Extracted visit data from systems.
- Standardized formats & removed duplicates.
- Enriched data with calculated fields.
Exploratory Analysis
- Identified high-volume temporal patterns.
- Analyzed satisfaction across demographics.
- Flagged unusual patient flow patterns.
Dashboard Development
- Created multi-page layout for different users.
- Implemented dynamic slicers & drill-throughs.
- Applied conditional formatting for KPIs.
Insight Generation
- Tracked trends in volume and satisfaction.
- Benchmarked wait times by department.
- Highlighted resource optimization needs.
Advanced DAX Measures
1. Patient Wait Time Analysis
Calculates the average wait time for patients, filtering for valid completed visits.
Avg Wait Time =
CALCULATE(
AVERAGE('Patient Visits'[Wait Time Minutes]),
'Patient Visits'[Status] = "Completed",
'Patient Visits'[Wait Time Minutes] > 0
)2. Rolling 30-Day Satisfaction
Uses time intelligence to look back at the previous 30 days to track satisfaction trends.
Satisfaction 30d Rolling =
CALCULATE(
[Average Satisfaction Score],
DATESINPERIOD(
'Calendar'[Date],
LASTDATE('Calendar'[Date]),
-30,
DAY
)
)3. Peak Hour Identification
Ranks hours of the day by patient volume to identify staffing bottlenecks.
Hour Rank =
RANKX(
ALL('Time'[Hour]),
[Total Visits],
,
DESC,
Dense
)Strategic Dashboard & Insights
Business Impact
- Reduced Wait Times: Identified bottlenecks to lower average patient wait times.
- Satisfication: Targeted service enhancements to improve patient scores.
- Staffing: Optimized staff allocation based on predicted volume patterns.
- Resource Allocation: Enhanced distribution of resources across departments.
Technical Proficiencies
- Power Query: Complex transformation, cleaning, and text parsing.
- Advanced DAX: Time intelligence, statistical measures, and ranking functions.
- Visualization: Multi-metric KPI cards, heat maps, and custom tooltips.
- Data Modeling: Star schema with role-playing dimensions.