Josephine-analyst/Public-Hospital-Utilization-Performance-Analysis

Power BI dashboard analyzing 4,853 admissions, 243 mortalities (5%) and KPIs across departments, states, gender and 20+ hospitals for Nigeria's public health sector. # PUBLIC HOSPITAL UTILIZATION & PERFORMANCE ANALYSIS ## PROJECT DESCRIPTION Interactive Power BI dashboard analyzing ~4,853 admissions, 243 mortalities (5% rate), and KPIs across departments, states, gender, and 20+ public hospitals in Nigeria. Built for resource allocation, capacity planning, and outcome monitoring in the public health sector. .png)](Screenshot%20(116).png ## 🎯 Business problem & Objectives - Track seasonal patient admissions and discharges - Identify high-utilization departments (e.g., Pediatrics, Surgery) and states (e.g., Kaduna) - Monitor mortality rates by department, gender, and hospital - Highlight capacity gaps and recommend staffing/training priorities ## 🛠️ Tools & Technologies - **Power BI Desktop** (Visualizations & Slicers) - **Power Query** (ETL/cleaning) - **Excel** (.xlsx source data) - **DAX Measures** (16+ Custom KPIs for dynamic KPIs) ## KEY DAX MEASURES AND CALCULATED TABLE // Date Table 'DATE TABLE' = CALENDARAUTO() *Extracting the month and day from the Date Table Month = FORMAT('DATE TABLE'[Date], "MMMM") Day = FORMAT('DATE TABLE'[Date], "DDDD") // Sample Measures TOTAL ADMISSIONS = SUM('Public_Hospital_Utilization_Dat'[Patients Admitted]) TOTAL DISCHARGES = SUM(Public_Hospital_Utilization_Dat[Patients Discharged]) TOTAL MORTALITY = SUM('Public_Hospital_Utilization_Dat'[Mortality Count]) MORTALITY RATE% = ('DATE TABLE'[TOTAL MORTALITY]/'DATE TABLE'[TOTAL ADMISSIONS]*100) DISCHARGE RATE = DIVIDE(SUM('Public_Hospital_Utilization_Dat'[Patients Discharged]),[TOTAL ADMISSIONS],0) AVERAGE LENGTH OF STAY = AVERAGE(Public_Hospital_Utilization_Dat[Avg Stay (Days)]) NET CHANGE IN PATIENTS = [TOTAL ADMISSIONS]-[TOTAL DISCHARGES] ## VISUALIZATION - **Patient Admission Counts by Month:** This Line Chart tracks monthly patient admission counts across 2022, starting low in January(~300), peaking sharply in July(~480), with subsequent spikes and dips(e.g., ~450 in late months), reflecting seasonal or event-driven surges in h …