Home Course home Curriculum Learner dashboard Skill tree Business projects Datasets Final project

Module 10 — Business analytics projects

Five briefs, five domains, five portfolio dashboards. Each one gives you a realistic dataset, a named stakeholder with a real decision to make, the questions the dashboard must answer, KPI definitions, the traps specific to that data, and a scoring rubric.

What each one does not give you is a click-by-click walkthrough. By this point in the course you have the technique; these projects are where you prove you can apply it to a problem nobody has solved for you.

How to get portfolio value out of these
A .pbix file on its own is weak portfolio evidence — a recruiter cannot open it and would not know what to look at if they could. What makes these projects count is the written case study around them: the business problem, what you found in the data, the decisions you made while cleaning and modelling, the KPI definitions, and what you would do differently. Module 14 Lesson 2 covers exactly how to write that, and it is worth doing for at least two of these five.
PROJECT 1

E-Commerce Intelligence

Analyse revenue, orders, customers, products, profit, regions and monthly trends for an online retailer, and deliver an executive sales dashboard.

Intermediate 6–8 hours Executive Sales Dashboard

You have joined a multi-channel retailer as their first data analyst. The commercial director has two years of order-line data and no reporting beyond a monthly spreadsheet that takes a day to assemble. She wants a single dashboard she can open before her Monday leadership meeting and answer any revenue question that comes up.

Commercial Director — reviews it weekly, presents from it monthly
Retail Sales Transactions15,067 rows · 13 columns · 1.3 MB ↓ Download Documentation

Questions the dashboard must answer

  • How is revenue tracking against last year, by month and cumulatively?
  • Which categories and products drive revenue, and which drive profit? They are not the same.
  • Which regions and channels are growing, and which are flat?
  • How much revenue is discounted, and what does discounting cost us in margin?
  • Who are our highest-value customers, and what share of revenue do the top 20% represent?

What you are working with

  • 15,067 order lines across 7,607 orders
  • 896 customers who actually purchased
  • Total net revenue of about $6.31M across the two years
  • Blended profit margin of about 19.5%
  • Electronics is the largest category by revenue but carries the thinnest margin (~13%)

Analyse

RevenueOrdersCustomersProductsProfitRegionsMonthly trends

KPI definitions

Define every one of these as a measure, and write the definition down. A KPI whose definition is not documented will be interpreted three different ways within a month.

KPIDefinitionWhy it matters / watch out for
Total RevenueSUM of NetSalesThe headline number. Everything else contextualises it.
Total ProfitSUM of ProfitRevenue minus COGS. Watch this diverge from revenue.
Profit Margin %DIVIDE(Total Profit, Total Revenue)The number that reveals mix problems.
Total OrdersDISTINCTCOUNT of OrderIDNot COUNTROWS — the fact table is at line level.
Average Order ValueDIVIDE(Total Revenue, Total Orders)Rising AOV with flat orders means upsell is working.
Active CustomersDISTINCTCOUNT of CustomerKeyCustomers who bought in the period.
Revenue YoY %Compare to SAMEPERIODLASTYEARRequires a marked date table.
Discount RateWeighted average discountWeight by revenue, not a simple average of the Discount column.

Traps specific to this dataset

These are the mistakes that actually get made on this data. Read them before you start, not after your numbers disagree with the source file.

The fact table is at line level, not order level
COUNTROWS(Sales) counts lines, not orders. Use DISTINCTCOUNT(Sales[OrderID]) for order count, or every average-order-value figure you produce will be wrong by a factor of about two.
StoreKey is blank for online and partner orders
That is correct data, not an error — those channels have no store. If you join to the store dimension carelessly you will silently drop about 70% of your revenue. Check your total against the raw file before trusting any store-sliced visual.
Two date columns exist
OrderDate and ShipDate. Only one relationship to the date table can be active. Decide which one the business means by "sales in March" and be explicit about it on the report.
Category sits in the product dimension, not the fact
You cannot slice by category until the relationship to bwb-products.csv exists. This is the point of Module 4.

This is one sensible order, not the only one. If you already know how you want to approach it, do that instead — the deliverable is what matters.

Load and inspect
Load bwb-retail-sales.csv, bwb-products.csv, bwb-customers.csv and bwb-stores.csv. In Power Query, check column quality on every column before you load. Confirm the row count matches the file.
Build a date table
Create one in DAX with CALENDAR or in Power Query, covering 2024-01-01 to 2025-12-31 with no gaps. Mark it as a date table. Time intelligence will silently misbehave if you skip this.
Build the star schema
Four dimensions around one fact. All relationships one-to-many, single direction, from dimension to fact. Check Model view: if any relationship is dashed or missing, fix it before writing a single measure.
Write the base measures
Total Revenue, Total Profit, Total Orders, Active Customers. Validate each against a simple aggregation of the raw file before building anything on top of them.
Write the derived measures
Profit Margin %, AOV, Revenue YoY %, Revenue YTD. Every one of these depends on the base measures being right, so validate in order.
Design the executive page
A KPI row across the top, a revenue trend with prior year, a category breakdown that shows revenue and margin together, and a region view. Follow the hierarchy rules from Module 8.
Add a detail page and drill-through
A product-level table users reach by drilling through from any category visual. This is what stops the executive page from becoming cluttered.
Validate before you ship
Total revenue on your dashboard must equal the sum of NetSales in the source file. If it does not, you have a relationship or filter problem — find it now, not after someone else does.

Deliverable

A .pbix file with at least two report pages: an executive summary and a detail page reachable by drill-through. The executive page must answer all five business questions above without the reader needing to change any filter.

How this is scored

Data Cleaning15%
Data Model20%
DAX20%
Visualization15%
Dashboard UX15%
Business Insights15%

Do not start these until the core dashboard answers every question above. A complete simple dashboard beats an incomplete clever one, in a portfolio and at work.

  • Add a Pareto analysis showing what share of revenue the top 20% of products generate
  • Build a discount-band dimension (0%, 1–10%, 11–20%, 21%+) in Power Query and show margin by band
  • Add a customer cohort view using SignupDate to show whether newer cohorts spend more or less
PROJECT 2

HR Analytics

Analyse headcount, attrition, departments, salaries, tenure, performance and demographics, and deliver an HR analytics dashboard for a people team.

Intermediate 5–7 hours HR Analytics Dashboard

The People Director has a retention problem and a board meeting in three weeks. She knows the headline attrition number and nothing else — not which departments are worst, not whether it is a pay problem or a management problem, and not whether it is getting better or worse. She needs evidence she can act on, not a wall of charts.

People Director — will present findings to the board
HR Employee Records1,200 rows · 15 columns · 117 KB ↓ Download Documentation

Questions the dashboard must answer

  • What is our attrition rate overall, and how does it vary by department and job level?
  • Does satisfaction score predict attrition better than salary does?
  • What is the tenure profile of leavers — are we losing people early or late?
  • Is there a pay gap by level once department is controlled for?
  • Which specific group should we intervene with first, and what is the evidence?

What you are working with

  • 1,200 employee records
  • 200 leavers — an overall attrition rate of about 16.7%
  • Customer Support has the highest departmental attrition at about 24%
  • Average salary across all levels is about $82,351

Analyse

Employee countAttritionDepartmentsSalariesTenurePerformanceDemographics

KPI definitions

Define every one of these as a measure, and write the definition down. A KPI whose definition is not documented will be interpreted three different ways within a month.

KPIDefinitionWhy it matters / watch out for
HeadcountCOUNTROWS of employees where Attrition = NoCurrent employees only.
Total EmployeesCOUNTROWS of all rowsIncludes leavers — needed as the attrition denominator.
Attrition Rate %DIVIDE(Leavers, Total Employees)Be explicit about the denominator on the report.
Average Tenure (years)Average of DATEDIFF(HireDate, today or ExitDate)Different for leavers and stayers.
Average SalaryAVERAGE of AnnualSalaryAlways segment this — the blended figure hides everything.
Average SatisfactionAVERAGE of SatisfactionScore1–5 scale. Track by department.
LeaversCOUNTROWS where Attrition = YesThe numerator.

Traps specific to this dataset

These are the mistakes that actually get made on this data. Read them before you start, not after your numbers disagree with the source file.

ExitDate is blank for current employees
That is a genuine null and it is correct. Do not fill it with today's date — that would make every current employee look like a leaver in any tenure calculation. Handle the two populations separately.
Attrition rate needs a stated denominator
Leavers ÷ total ever employed is not the same as leavers ÷ average headcount, and HR professionals will ask which you used. Pick one, state it on the report, and be consistent.
Gender includes 'Prefer not to say'
Do not drop those rows to make a chart cleaner. Show the category, or state clearly that it is excluded and why. Quietly removing people from a diversity analysis is not acceptable.
Small departments produce unstable percentages
A department with 80 people showing 20% attrition is 16 people. Show the count alongside every rate, or a small team with three leavers will look like a crisis.

This is one sensible order, not the only one. If you already know how you want to approach it, do that instead — the deliverable is what matters.

Load and profile
Load bwb-hr-employees.csv. Check column quality — pay particular attention to the blank ExitDate column and confirm the blanks are where you expect them.
Add tenure in Power Query
Add a Tenure Years column: for leavers use ExitDate minus HireDate; for current employees use today minus HireDate. Handle the null branch explicitly.
Add a tenure band column
Conditional column: under 1 year, 1–2, 2–5, 5–10, 10+. Banding turns a continuous variable into something a board slide can actually show.
Build the date dimension
Even though this is not a transactional dataset, a date table lets you show hires and exits over time, which is how you answer "is it getting worse".
Write the measures
Headcount, Leavers, Attrition Rate %, Average Tenure, Average Satisfaction. Validate the attrition rate against a manual count before proceeding.
Analyse the drivers
Build a view comparing attrition rate across department, level, satisfaction band, tenure band and work mode. Look for where the rate deviates most from the overall average — that is your finding.
Design for the board
One page. The headline rate, the two or three segments that deviate most, and the evidence for the recommendation. Resist the urge to show everything you found.
Write the recommendation
One paragraph naming the group, the evidence, the proposed intervention and what you would measure to know it worked. This is the deliverable that matters.

Deliverable

A .pbix file with a single board-ready page plus a supporting detail page, and a written recommendation of no more than 200 words naming one specific intervention and the evidence behind it.

How this is scored

Data Cleaning15%
Data Model20%
DAX20%
Visualization15%
Dashboard UX15%
Business Insights15%

Do not start these until the core dashboard answers every question above. A complete simple dashboard beats an incomplete clever one, in a portfolio and at work.

  • Build a satisfaction-vs-attrition scatter at department level to show the relationship visually
  • Add a survival-style view: what proportion of each hire cohort is still employed after 1, 2 and 3 years
  • Calculate the estimated cost of attrition using a replacement-cost assumption you state openly
PROJECT 3

Financial Performance

Analyse revenue, costs, profit, margin and budget-vs-actual variance by month, and deliver a CFO dashboard that survives a board meeting.

Advanced 6–8 hours CFO Dashboard

The CFO needs a monthly performance pack that replaces the current 40-slide deck. She wants actual versus budget by account and cost centre, with variances that are immediately readable as favourable or unfavourable — which is harder than it sounds, because a negative variance on a cost line is good news.

CFO — uses it monthly, shares it with the board quarterly
Finance — Actuals vs Budget722 rows · 8 columns · 56 KB ↓ Download Documentation

Questions the dashboard must answer

  • What is revenue, cost and profit for the period, versus budget?
  • Which cost centres are consistently over budget, and by how much?
  • What is the gross margin trend, and is it improving?
  • Which single account drives the largest absolute variance?
  • Does favourable revenue variance offset unfavourable cost variance overall?

What you are working with

  • 722 ledger rows across 24 months
  • Revenue accounts are stored positive; COGS and OpEx are stored negative
  • Ten accounts across five cost centres
  • Both 2024 and 2025 are present, so year-over-year comparison is possible

Analyse

RevenueCostsProfitMarginBudget vs actualMonthly performance

KPI definitions

Define every one of these as a measure, and write the definition down. A KPI whose definition is not documented will be interpreted three different ways within a month.

KPIDefinitionWhy it matters / watch out for
Total RevenueSUM of ActualAmount where AccountType = RevenuePositive sign convention.
Total CostsSUM of ActualAmount where AccountType in (COGS, OpEx)Stored negative — decide how to display.
Gross ProfitRevenue + COGSPlus, not minus — COGS is already negative.
Gross Margin %DIVIDE(Gross Profit, Revenue)The trend line the CFO watches.
Net ProfitSUM of all ActualAmountWorks because of the sign convention.
Budget VarianceActual − BudgetRaw arithmetic difference.
Variance %DIVIDE(Variance, ABS(Budget))ABS in the denominator, or negative budgets flip the sign.
Favourable / UnfavourableSWITCH on AccountType and variance signThe measure that makes the report readable.

Traps specific to this dataset

These are the mistakes that actually get made on this data. Read them before you start, not after your numbers disagree with the source file.

The sign convention will catch you out
Cost accounts are negative. SUM(ActualAmount) across everything gives net profit, which is correct and probably not what your "Total Costs" card should show. Decide whether to display costs as positive magnitudes and apply that consistently.
Favourable is not the same as positive
Revenue above budget is favourable (positive variance). Cost above budget is unfavourable (also positive variance, because costs are negative and a larger negative is a smaller number). You need a SWITCH on AccountType to classify correctly — this is the single most important measure in the project.
Variance % divides by a negative
DIVIDE(Variance, Budget) on a cost account flips the sign. Use ABS(Budget) in the denominator.
PeriodStart is the first of the month
This is monthly data, not daily. Your date table should still be daily, but every visual should aggregate to month — showing a daily axis on monthly data produces a comb.

This is one sensible order, not the only one. If you already know how you want to approach it, do that instead — the deliverable is what matters.

Load and understand the sign convention
Load bwb-finance-actuals-budget.csv. Before writing anything, sum ActualAmount by AccountType and confirm you understand which are positive and which are negative.
Build account and cost centre dimensions
Extract distinct accounts and cost centres into their own tables in Power Query using Remove Duplicates on a reference query. This is a good exercise in building dimensions from a flat file.
Add an account ordering column
P&L accounts have a conventional order — revenue, COGS, gross profit, opex. Add a sort column so your matrix does not present them alphabetically.
Build a monthly date table
Daily granularity, marked as a date table, covering both years.
Write the base measures
Total Revenue, Total Costs, Gross Profit, Net Profit. Validate each against a pivot of the raw file.
Write the variance measures
Budget Variance, Variance %, and the Favourable/Unfavourable classification. Test the classification on one revenue account and one cost account explicitly.
Build the P&L matrix
Accounts down, months across, with conditional formatting driven by the favourable measure — not by the raw variance sign.
Add the waterfall
A waterfall from budgeted profit to actual profit, bridged by the largest variance drivers. This is the visual the CFO will actually use in the board meeting.

Deliverable

A .pbix with an executive summary page, a P&L matrix page with correct favourable/unfavourable formatting, and a variance analysis page including a waterfall bridging budget to actual.

How this is scored

Data Cleaning15%
Data Model20%
DAX20%
Visualization15%
Dashboard UX15%
Business Insights15%

Do not start these until the core dashboard answers every question above. A complete simple dashboard beats an incomplete clever one, in a portfolio and at work.

  • Add a rolling 12-month view so seasonality does not distort month-on-month comparison
  • Build a cost centre scorecard ranking centres by consistency of budget adherence, not just size of variance
  • Add a what-if parameter letting the CFO model a percentage cost reduction and see the profit impact
PROJECT 4

Healthcare Analytics

Analyse patients, departments, treatment, costs, outcomes and utilisation, and deliver an operations dashboard for a hospital management team.

Advanced 6–8 hours Healthcare Operations Dashboard

A hospital operations director needs to understand where capacity and cost are going. Bed days are the constraint, readmissions are a quality signal the regulator watches, and cost per encounter varies far more between departments than anyone can currently explain.

Head of Operations — reviews weekly with clinical leads
Healthcare Encounters3,000 rows · 14 columns · 357 KB ↓ Download Documentation

Questions the dashboard must answer

  • How many encounters and distinct patients did we handle, and how is that trending?
  • Which department has the highest cost per encounter, and does severity explain it?
  • What is our 30-day readmission rate, and does it vary by admission type or department?
  • How does length of stay distribute — and is the mean misleading?
  • Where are bed days concentrated, and which department is the capacity constraint?

What you are working with

  • 3,000 encounters across one calendar year
  • Overall 30-day readmission rate of about 5.5%
  • Average length of stay of about 3.4 days
  • Seven departments with genuinely different cost and stay profiles

Analyse

PatientsDepartmentsTreatmentCostsOutcomesUtilisation

KPI definitions

Define every one of these as a measure, and write the definition down. A KPI whose definition is not documented will be interpreted three different ways within a month.

KPIDefinitionWhy it matters / watch out for
Total EncountersCOUNTROWSOne row per encounter.
Distinct PatientsDISTINCTCOUNT of PatientIDPatients can appear more than once — this gap matters.
Average Length of StayAVERAGE of LengthOfStayDaysReport the median alongside it.
Total Bed DaysSUM of LengthOfStayDaysThe real capacity measure.
Total CostSUM of TotalCostWhole-encounter cost.
Cost per EncounterDIVIDE(Total Cost, Total Encounters)Compare across departments.
Readmission Rate %DIVIDE(Readmitted encounters, Total Encounters)The quality signal.
Average SeverityAVERAGE of SeverityScoreContext for cost comparisons.

Traps specific to this dataset

These are the mistakes that actually get made on this data. Read them before you start, not after your numbers disagree with the source file.

Encounters are not patients
COUNTROWS gives encounters; DISTINCTCOUNT(PatientID) gives people. Confusing them makes "patients treated" look far higher than it is. Show both and label them precisely.
Length of stay is heavily skewed
A few very long stays pull the mean well above the median. Report both, and use a histogram rather than a single average card if the distribution is the point.
Zero-day stays are real
Same-day discharge is legitimate, not missing data. Do not filter zeros out — in some departments they are a large share of activity.
Never compare cost across departments without severity
Oncology costing more than Paediatrics is not inefficiency. Any cost comparison must show severity alongside it, or the report will be actively misleading to a clinical audience.

This is one sensible order, not the only one. If you already know how you want to approach it, do that instead — the deliverable is what matters.

Load and profile
Load bwb-healthcare-encounters.csv. Examine the distribution of LengthOfStayDays before deciding how to summarise it.
Build department and date dimensions
Extract a department dimension. Build a date table on AdmitDate covering the full year.
Add derived columns in Power Query
Age band (0–17, 18–39, 40–64, 65+) and a length-of-stay band. Both make the analysis presentable without losing the underlying detail.
Write the base measures
Encounters, Distinct Patients, Total Bed Days, Total Cost. Validate against the raw file.
Write the rate measures
Cost per Encounter, Readmission Rate %, Average and Median length of stay. Median needs MEDIAN, and showing it beside the mean is the point.
Build the utilisation view
Bed days by department and month. This is what identifies the capacity constraint.
Build the cost view with severity context
Cost per encounter by department, with average severity displayed on the same visual — a combo chart or a scatter with severity on one axis.
Build the quality view
Readmission rate by department and admission type, with encounter counts shown so small denominators are visible.

Deliverable

A .pbix with three pages — operations overview, cost analysis with severity context, and quality/readmissions — plus a written note on which single department you would investigate first and why.

How this is scored

Data Cleaning15%
Data Model20%
DAX20%
Visualization15%
Dashboard UX15%
Business Insights15%

Do not start these until the core dashboard answers every question above. A complete simple dashboard beats an incomplete clever one, in a portfolio and at work.

  • Add a length-of-stay histogram with the mean and median marked, to demonstrate the skew explicitly
  • Build a payer-mix view showing cost recovery profile by payer type
  • Model the bed-day impact of reducing average length of stay by half a day in the highest-volume department
PROJECT 5

Supply Chain Intelligence

Analyse inventory, suppliers, delivery time, delays, product movement and warehouse performance, and deliver a supply chain intelligence dashboard.

Advanced 7–9 hours Supply Chain Intelligence Dashboard

A supply chain manager is renegotiating supplier contracts next quarter. She needs a supplier scorecard grounded in delivery data rather than relationships, and she needs to know which warehouses and categories are most exposed to late delivery.

Supply Chain Manager — using it directly in contract negotiations
Supply Chain Purchase Orders2,200 rows · 21 columns · 319 KB ↓ Download Documentation

Questions the dashboard must answer

  • Which suppliers miss their promised lead time most often, and by how much?
  • Does a higher supplier quality score actually correlate with on-time delivery?
  • Which warehouse absorbs the most late deliveries?
  • What is the fill-rate distribution, and which category is worst affected?
  • Which three suppliers should we renegotiate first, and what is the evidence?

What you are working with

  • 2,200 purchase orders across one year
  • About 48% of orders arrive later than promised
  • Average delay of about 1.2 days across all orders
  • Seven suppliers across four warehouses

Analyse

InventorySuppliersDelivery timeDelaysProduct movementWarehouse performance

KPI definitions

Define every one of these as a measure, and write the definition down. A KPI whose definition is not documented will be interpreted three different ways within a month.

KPIDefinitionWhy it matters / watch out for
Total Purchase OrdersCOUNTROWSOne row per PO.
On-Time Delivery %DIVIDE(On-time POs, Total POs)The headline supplier metric.
Average Delay DaysAVERAGE of DelayDaysNegative means early — do not clamp to zero.
Fill Rate %DIVIDE(SUM QuantityReceived, SUM QuantityOrdered)Weighted, not an average of ratios.
Total Purchase CostSUM of PurchaseCostSpend, for weighting supplier importance.
Severely Late %DIVIDE(Severely Late POs, Total POs)More than 7 days late.
Supplier Quality ScoreAVERAGE of SupplierQualityScoreStated score, to test against actual performance.

Traps specific to this dataset

These are the mistakes that actually get made on this data. Read them before you start, not after your numbers disagree with the source file.

Fill rate must be weighted
Averaging the per-order fill ratio treats a 50-unit order the same as a 1,000-unit order. Divide total received by total ordered instead. This is the classic weighted-average trap and it appears in almost every supply chain report.
Negative delay is meaningful
Early delivery is real and, for some categories, a cost. Do not clamp DelayDays to zero — you lose the information that a supplier is consistently early.
On-time % alone ranks suppliers badly
A supplier with 20 orders at 90% on-time is not clearly better than one with 400 orders at 85%. Weight by spend or volume, and show the order count on every ranking visual.
Quality score is a stated figure, not an outcome
It is what the supplier claims or what was assessed. Testing it against actual delivery performance is one of the more interesting findings available in this dataset — and it may not correlate.

This is one sensible order, not the only one. If you already know how you want to approach it, do that instead — the deliverable is what matters.

Load and profile
Load bwb-supply-chain.csv. Check the DelayDays distribution — note the negative values and the long right tail from disrupted orders.
Build supplier and warehouse dimensions
Extract distinct suppliers (with country and quality score) and warehouses into dimension tables. The flat file has them denormalised.
Build the date table
On OrderDate, covering the full year.
Write the base measures
Total POs, Total Purchase Cost, on-time and late counts. Validate the status counts against the DeliveryStatus column.
Write the rate measures
On-Time Delivery %, Fill Rate % (weighted), Average Delay Days, Severely Late %.
Build the supplier scorecard
A matrix with suppliers down and metrics across, conditionally formatted. Include order count and spend so the reader can judge whether a rate is meaningful.
Test the quality-score hypothesis
A scatter of stated quality score against actual on-time percentage, bubble-sized by spend. State plainly whether the relationship holds.
Build the exposure view
Late deliveries by warehouse and category, so the manager can see where the operational pain actually lands.

Deliverable

A .pbix with a supplier scorecard page, a delivery performance page and a warehouse exposure page, plus a one-page written recommendation naming three suppliers to renegotiate with the evidence for each.

How this is scored

Data Cleaning15%
Data Model20%
DAX20%
Visualization15%
Dashboard UX15%
Business Insights15%

Do not start these until the core dashboard answers every question above. A complete simple dashboard beats an incomplete clever one, in a portfolio and at work.

  • Add a lead-time reliability measure using standard deviation of delay — consistency matters as much as average
  • Build a spend-at-risk measure: total purchase cost flowing through suppliers below a chosen on-time threshold
  • Add a what-if parameter for an acceptable on-time threshold and let the scorecard reclassify suppliers live
Which order should you do them in?
Project 1 first — it uses the dataset you have seen throughout the course, so the only new thing is doing it unaided. After that, pick by the domain closest to the job you want. If you want a finance role, do Project 3 properly and write the case study. Two deeply finished projects beat five rushed ones, every time.
Ready for the final project? →