�� Group Project Task:
Data Analytics & Dashboard Design (Power
BI) Duration: 1 Week (11th September – 18th September 2025)
Group A – Healthcare_Project_Dataset
Title: Hospital Operations & Patient Analytics Dashboard
Objective:
Analyze hospital data (patients, staff, departments, and bed utilization) to gain insights into
hospital performance, patient distribution, and resource allocation. Build an interactive
Power BI dashboard for decision-making.
Tasks:
1. Data Cleaning & Preparation
● Remove duplicate patient/staff records.
● Handle missing values in Age, Gender, Bed allocation
● Standardize date columns (Admission Date, Discharge Date).
● Build star schema:
○ Fact: Patient Admissions (Admission Date, Discharge Date, Bed ID,
Department ID, Staff ID).
○ Dimensions: Patient, Staff, Department, Bed.
2. Data Analytics
● Patient admissions by age, gender, and department.
● Bed occupancy rate (occupied vs available).
● Staff-to-patient ratio by department.
● Average length of stay for patients.
● Most utilized departments and busiest doctors.
● Identify underutilized departments/beds.
3. Data Visualization (Power BI)
● KPI Cards: Total Patients, Avg Length of Stay, Bed Occupancy %, Total Staff.
● Bar chart: Patients by Department.
● Pie chart: Patient distribution by Gender.
● Line chart: Admissions trend over time.
● Map (if location data available): Patients by City/Region.
● Table (with conditional formatting): Bed utilization (highlight over 90% usage).
● Stacked chart: Staff workload by department.
4. Dashboard Design
● Slicers/Filters: Department, Gender, Age Group, Time Period.
● Drill-through page for Patient Profile or Department Analysis.
● Use tooltips to show extra details
● Ensure clean, hospital-themed design
Guided DAX Exercises (For Learners)
1. Total Patients
Count distinct Patient IDs.
2. Bed Occupancy %
Divide occupied beds by total beds.
3. Avg Length of Stay
Use DATEDIFF between Admission Date & Discharge Date.
4. Total Staff
Count distinct Staff IDs.
5. Patients per Department
Use COUNTROWS grouped by Department ID.
Appending Queries (Power Query)
1. Go to Home → Get Data → Excel and load all patient files.
2. In Power Query Editor → select Append Queries
3. Combine them into one master Patient Detail table.
4. Ensure column headers match
5. Load into Power BI for analysis.
Group B – Sales Dataset - Project
Title: Sales Analytics Dashboard (Power BI)
Objective:
Analyze the Customer, Product, and Sales tables to gain insights into revenue, profitability,
and customer-product trends. Build an interactive Power BI dashboard for
decision-making.
Tasks
1. Data Cleaning & Preparation
● Remove duplicates and handle missing values.
● Ensure correct data types (Dates, Numbers, Categories).
● Append multiple sales files using Power Query → Append Queries (combine
yearly/quarterly sales files into one Sales fact table).
● Build star schema:
○ Fact: Sales (Sales, Quantity, Discount, Profit).
○ Dimensions: Customer (Customer ID), Product (Product ID)
2. Data Analytics
● Identify top-performing categories, sub-categories, and products.
● Customer analysis by Segment, Region, Age group.
● Compare Sales vs. Profit across products/customers.
● Detect loss-making products/categories.
● Order trends: Monthly/Quarterly/Yearly sales.
3. Data Visualization (Power BI)
● KPI Cards: Total Sales, Profit, Orders, Avg. Discount, Profit Margin.
● Line chart: Sales & Profit trend over time.
● Bar chart: Sales vs Profit by Category/Sub-Category. ● Top
10 Products & Customers: Highlight best performers. ● Table
(with conditional formatting): Loss-making products. ● Map:
Region/Country-wise sales & profit.
● Stacked chart: Segment contribution to revenue.
4. Dashboard Design
● Slicers/Filters: Category, Region, Segment, Time Period.
● Drill-down to Product and Customer details.
● Drill-through page for Customer Profile or Product Performance.
● Use bookmarks & tooltips for interactivity.
● Ensure user-friendly layout and performance optimization.
Guided DAX Exercises (For Learners)
1. Total Sales
Use the SUM function on the Sales column.
2. Total Profit
Either SUM the Profit column OR calculate as Sales – Cost.
3. Total Orders
Use the DISTINCTCOUNT function on the Order ID column.
4. Average Discount
Apply the AVERAGE function on the Discount column.
5. Profit Margin %
Use DIVIDE to calculate Profit ÷ Sales. Add 0 as the alternate result.
Appending Queries (Power Query)
1. Go to Home → Get Data → Excel/CSV and load all sales files.
2. In Power Query Editor → select Home → Append Queries. 3.
Combine them into one master Sales table.
4. Ensure column names match before appending (Order ID, Sales, Profit, etc.).
5. Load back into Power BI for modeling.
Deliverable
A fully interactive Power BI Dashboard with:(Both Groups)
● KPIs, charts, and insights
● Appended Sales Data queries
● DAX measures
● Dashboard( mininum 2 pages) summarizing insights from analysis.
● Group Presentation (10–15 mins) explaining findings and demonstrating the
dashboard.
Evaluation Criteria
● Data Cleaning & Preparation (20%) → Handling missing/duplicate values,
consistency.
● Analytics & Insights (25%) → Depth and quality of analysis.
● Visualizations (25%) → Effectiveness, variety, and clarity of charts.
● Dashboard Design (20%) → Interactivity, KPIs, professional look. ●
Presentation (10%) → Communication and explanation of results.