{"id":40,"date":"2026-09-30T06:15:41","date_gmt":"2026-09-30T06:15:41","guid":{"rendered":"https:\/\/www.datascientist.ca\/blog\/?p=40"},"modified":"2026-10-01T03:42:45","modified_gmt":"2026-10-01T03:42:45","slug":"healthcare-data-analytics-project-using-microsoft-power-bi","status":"publish","type":"post","link":"https:\/\/www.datascientist.ca\/blog\/healthcare-data-analytics-project-using-microsoft-power-bi\/","title":{"rendered":"Healthcare Data Analytics Project using Microsoft Power BI"},"content":{"rendered":"\n<iframe loading=\"lazy\" title=\"HealthCraeDashboard_Mar03-2026\" width=\"600\" height=\"373.5\" src=\"https:\/\/app.powerbi.com\/view?r=eyJrIjoiY2Q5NGFlYzYtZDZlZC00NGZhLWI4NDYtZTk1OTU0N2Y0OTFmIiwidCI6IjRhNjk1YWI3LWViNzktNDViZS05NTg2LWQ4NTA4ODUwYTY1NSJ9\" frameborder=\"0\" allowFullScreen=\"true\"><\/iframe>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Healthcare Data Analytics Project using Microsoft Power BI\" width=\"500\" height=\"281\" src=\"https:\/\/www.youtube.com\/embed\/E9qoZFSJO4g?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Case study overview: one dashboard for hospital management reporting<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>The business problem.<\/strong> 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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>The solution.<\/strong> 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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Who it is for.<\/strong> Data analysts, Power BI developers, business intelligence professionals, hospital managers, and students learning professional dashboard design.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Understanding the dataset<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">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. <em>[Adjust this list to the columns in your files, and name the source and the number of records.]<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Data quality issues to handle<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Dates arrive as text and must be converted before length of stay can be calculated.<\/li>\n\n\n\n<li>Names and categories may differ in capitalization or spelling.<\/li>\n\n\n\n<li>Duplicate records and negative or unusual billing values need checking.<\/li>\n\n\n\n<li>Free-text fields should be turned into consistent categories.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">A public tutorial should use synthetic or fully anonymized data, and the published report should never expose real patient identifiers.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Designing a proper data model<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The model follows a star schema: one fact table of admissions surrounded by dimension tables. This keeps filtering predictable and the report fast.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><th>Table<\/th><th>Type<\/th><th>Typical contents<\/th><\/tr><tr><td>Admissions<\/td><td>Fact<\/td><td>One row per admission, with keys, admission and discharge dates, billing amount<\/td><\/tr><tr><td>Patient<\/td><td>Dimension<\/td><td>Patient ID, age, age group, gender<\/td><\/tr><tr><td>Condition<\/td><td>Dimension<\/td><td>Medical condition, admission type<\/td><\/tr><tr><td>Doctor<\/td><td>Dimension<\/td><td>Doctor, hospital<\/td><\/tr><tr><td>Insurance<\/td><td>Dimension<\/td><td>Insurance provider<\/td><\/tr><tr><td>Date<\/td><td>Dimension<\/td><td>Date, month, quarter, year, marked as the date table<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Modelling steps<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Clean the data in Power Query and set correct data types.<\/li>\n\n\n\n<li>Create length of stay and age group columns.<\/li>\n\n\n\n<li>Split descriptive attributes into dimension tables with keys.<\/li>\n\n\n\n<li>Relate each dimension to the fact table, one-to-many, with single-direction filtering.<\/li>\n\n\n\n<li>Hide keys and raw numeric columns, so report authors use measures.<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\">DAX measures and executive KPIs<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Total Patients = DISTINCTCOUNT ( Admissions&#91;PatientID] )\n\nTotal Admissions = COUNTROWS ( Admissions )\n\nTotal Billing = SUM ( Admissions&#91;BillingAmount] )\n\nAvg Billing per Admission = DIVIDE ( &#91;Total Billing], &#91;Total Admissions] )\n\nAvg Length of Stay =\nAVERAGEX (\n    Admissions,\n    DATEDIFF ( Admissions&#91;AdmissionDate], Admissions&#91;DischargeDate], DAY )\n)\n\nAvg Patient Age = AVERAGE ( Patient&#91;Age] )\n\nEmergency Admissions % =\nDIVIDE (\n    CALCULATE ( &#91;Total Admissions], Admissions&#91;AdmissionType] = \"Emergency\" ),\n    &#91;Total Admissions]\n)\n\nAdmissions Prior Year =\nCALCULATE ( &#91;Total Admissions], SAMEPERIODLASTYEAR ( 'Date'&#91;Date] ) )\n\nAdmissions YoY % =\nDIVIDE ( &#91;Total Admissions] - &#91;Admissions Prior Year], &#91;Admissions Prior Year] )<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Dashboard walkthrough: Patient Summary and Patient Detail<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The report has two layers, so executives and analysts each get the view they need.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><th>Page<\/th><th>Audience<\/th><th>Main visuals<\/th><\/tr><tr><td>Patient Summary<\/td><td>Executives<\/td><td>KPI cards, admissions by month, billing by condition, admission type split, age group and gender breakdown<\/td><\/tr><tr><td>Patient Detail<\/td><td>Analysts and clinical managers<\/td><td>Table of admissions with condition, doctor, stay length and billing, filtered by slicers<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Gradient formatting.<\/strong> Conditional formatting colours values on a scale, so high and low results stand out without reading every number. Apply it to:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Matrix cells, such as billing by condition and age group<\/li>\n\n\n\n<li>Table columns, such as length of stay and billing amount<\/li>\n\n\n\n<li>Bar charts, where colour follows the value<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Use one colour scale consistently, with accessible contrast, and avoid red and green alone.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Navigation and layout<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Slicers for date, condition, admission type and hospital are synced across pages.<\/li>\n\n\n\n<li>KPI cards sit at the top, with detail below, so the page reads from summary to specifics.<\/li>\n\n\n\n<li>Drill-through from a chart opens the Patient Detail page for the selected group.<\/li>\n\n\n\n<li>A consistent theme, spacing and titles make the report feel professional.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Explore the working report in the <a target=\"_blank\" rel=\"noopener\" href=\"https:\/\/www.datascientist.ca\/blog\/healthcare-data-analytics-project-using-microsoft-power-bi\/\">live dashboard<\/a>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Insights, business value and lessons learned<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>What the dashboard reveals<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><em>[Volume: give total patients, admissions and billing for your selected period.]<\/em><\/li>\n\n\n\n<li><em>[Conditions: name the condition with the most admissions and the one with the highest average billing.]<\/em><\/li>\n\n\n\n<li><em>[Stay: give the average length of stay and the condition or admission type with the longest stays.]<\/em><\/li>\n\n\n\n<li><em>[Demographics: describe the age group and gender with the most admissions.]<\/em><\/li>\n\n\n\n<li><em>[Trend: describe how admissions changed by month or year.]<\/em><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Replace each prompt with the figure shown in your live dashboard.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Business value<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Executives get a one-page read on volumes, stays and costs.<\/li>\n\n\n\n<li>Analysts can drill from a trend to the patient records behind it.<\/li>\n\n\n\n<li>Reusable measures keep numbers consistent across reports and meetings.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Lessons for Power BI builders<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Design the data model before any visuals, because every measure depends on it.<\/li>\n\n\n\n<li>Build measures once and reuse them across pages.<\/li>\n\n\n\n<li>Use gradient formatting sparingly and consistently, so colour carries meaning.<\/li>\n\n\n\n<li>Separate summary and detail views, so each audience is not overloaded.<\/li>\n\n\n\n<li>Test measures against a hand-checked sample before publishing.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Frequently asked questions<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>What is a hospital analytics dashboard?<\/strong> It is an interactive report that summarizes patient, admission and billing data, so managers can track volumes, length of stay and costs.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>What KPIs should a hospital dashboard include?<\/strong> Common KPIs are patients, admissions, average length of stay, average billing, emergency admission share and year-over-year change in admissions.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>How do I calculate length of stay in Power BI?<\/strong> Use <code>DATEDIFF<\/code> between the admission and discharge dates, in days, then average it with <code>AVERAGEX<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>What is gradient formatting in Power BI?<\/strong> Gradient formatting is conditional formatting that colours cells or bars along a scale, so high and low values are easy to spot.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Why use a star schema for healthcare data?<\/strong> 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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Try it yourself<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Explore the <a target=\"_blank\" rel=\"noopener\" href=\"https:\/\/www.datascientist.ca\/blog\/healthcare-data-analytics-project-using-microsoft-power-bi\/\">live dashboard<\/a>, download the <a target=\"_blank\" rel=\"noopener\" href=\"https:\/\/drive.google.com\/drive\/folders\/1ocvJSk1_1_ak5yGDYRTgOL2KX-ug21rz?usp=sharing\">complete project files<\/a>, and follow the <a target=\"_blank\" rel=\"noopener\" href=\"https:\/\/www.youtube.com\/playlist?list=PLEEMXSxsawLWeAYNOt5vsxVlb6Y2SdF7C\">complete free Power BI course<\/a> to build it yourself. Need a hospital or healthcare reporting dashboard for your organization? Contact datascientist.ca.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Case study overview: one dashboard for hospital management reporting This Power BI hospital analytics dashboard brings patient, admission and cost [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[1],"tags":[],"class_list":["post-40","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/posts\/40","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/comments?post=40"}],"version-history":[{"count":2,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/posts\/40\/revisions"}],"predecessor-version":[{"id":72,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/posts\/40\/revisions\/72"}],"wp:attachment":[{"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/media?parent=40"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/categories?post=40"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/tags?post=40"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}