Lab: Real-Life Hospital Example

Real-Life Hospital Example

Hospital leadership wants to know whether no-shows are trending up month over month, and which doctors generate the most revenue so far this year.

Practical Exercise (Hands-On)

Build a Monthly Revenue Trend line chart, a Running Total Revenue measure, and a Doctor Revenue Rank measure using RANKX. Add a line chart of appointment counts by month, split by status.

Step-by-Step 

⚠️ Caution: This lab was developed using Microsoft Power BI Desktop v2.155.756.0 (64-bit). If you are using a different version, you may notice minor differences in the menus, icons, or interface. 

1. Confirm your Date table (from Chapter 3) is marked as a Date table: select it in the Data pane, go to Table Tools > Mark as Date Table, and choose the date column.
2. Right-click billing, New measure: Running Total Revenue = CALCULATE(SUM(billing[amount]), FILTER(ALLSELECTED(‘Date’), ‘Date'[Date] <= MAX(‘Date'[Date]))). Format as Currency.
3. Right-click billing, New measure for a doctor leaderboard: Doctor Revenue Rank = RANKX(ALL(doctors[first_name], doctors[last_name]), [Total Revenue], , DESC).
4. Add a Line Chart, drag the Date table’s Month field to the X-axis and Total Revenue to Values, to build the Monthly Revenue Trend.
5. Add a second Line Chart, drag Month to the X-axis, appointments[appointment_id] to Values (Count), and appointments[status] to Legend, to see appointment counts by month split by status.
6. Add a Table visual with doctors[first_name], the Total Revenue measure, and the Doctor Revenue Rank measure, then sort by rank ascending.
7. Filter this table to Doctor Revenue Rank <= 3 (or use a Top N filter) to build a “Top 3 Doctors by Revenue” leaderboard.
8. Add the Running Total Revenue measure to a line chart’s Values field alongside the Date axis and confirm it climbs steadily across months rather than jumping up and down.
9. Save the file.

Expected Result

A monthly revenue trend line, an appointments-by-month-and-status line chart, a smoothly climbing running total line, and a Top 3 Doctors by Revenue leaderboard table.

Mini Lab / Scenario Task

Build a Top 3 Doctors by Revenue table using RANKX combined with a filter, and format it as a leaderboard visual.