An operational, patient outcome, and financial performance analysis for MediTrack Global Health, an international healthcare organisation modelled on the UK's NHS, operating hospitals and medical centres across 10 countries. The project transforms a single large Excel flat file — mixing patient, doctor, department, hospital, and financial data — into a normalised star-schema data model in Power BI, with DAX measures and a 3-page interactive report built to support hospital management decision-making.
- Power BI — Data modelling, relationships, DAX measures, and report design
- Power Query — Transforming and normalising the source flat file into fact and dimension tables
- DAX — Custom measures for operational, patient outcome, and financial KPIs
- Star-schema data model — one flat file normalised into a
FactTable(admissions, costs, outcomes) connected toPatientDim,DoctorDim,DepartmentDim,HospitalDim, andCalendarDim - Operations page — admissions trend (2023 vs 2024), patients by department, admissions by hospital, and a country-level admissions map, with bed occupancy rate and patients-per-doctor KPIs
- Patients page — recovery, mortality, and readmission rate by hospital and department, patient demographics, and a monthly outcomes pivot table
- Financials page — profit and cost by month/hospital/department, a cost decomposition tree, reimbursement rate, and a country-level profitability map
- Cross-filtering slicers — Year, Country, and Hospital slicers on every page for management drill-down
A single Excel flat file (MediTrackData.xlsx, included in this repo) covering 4,000 hospital admissions across 6 hospitals in 10 countries — patient details, doctor and department assignments, treatment records, and full cost/revenue breakdown per admission (treatment cost, medication cost, diagnostic cost, room charges, insurance reimbursement, and net income). Provided as part of a 10Alytics Power BI case study; normalised into the star schema shown above.
- 4,000 admissions across 6 hospitals and 10 countries (2023–2024), with the UK accounting for 41% of volume (1,642 admissions) — consistent with the model's UK-headquartered structure
- Overall Recovery Rate is 74.5%, but it varies sharply by department: Maternity recovers at 95.5%, while Oncology sits at just 49.8% — the widest clinical gap in the model
- Oncology is both the hardest and most expensive department — its 49.8% recovery rate pairs with £8.07M in total expenditure, the highest of any department (nearly double Neurology, the next-highest at £5.58M). This combination is worth a dedicated management conversation, not just a KPI footnote
- Mortality is concentrated in Neurology (5.9%) and Emergency (5.1%) — both meaningfully above the 3.4% overall average
- Profit and clinical quality don't move together: Royal Free Hospital has the highest recovery rate (77.1%) but the lowest profit (£396,930) of the six hospitals, while Chelsea & Westminster leads on profit (£510,063) with a mid-table recovery rate (74.5%)
- Financial position is healthy but thin: £40.3M billed against £34.4M in costs yields a 6.8% profit margin, with a strong 92.1% insurance reimbursement rate — but £3.2M in claims remain outstanding
- Year-on-year growth: admissions rose 4% (1,960 → 2,040) and profit rose 6.4% (£1.33M → £1.42M) from 2023 to 2024, suggesting efficiency improved slightly alongside volume growth
- Readmission rate sits at 9.8% and average bed occupancy at 75.3%, with a 5.65-day average wait time — reasonable operational headroom before capacity becomes a constraint
*Built by Emmanuel Sekyere