Lab: DAX Fundamentals & Healthcare KPIs

Real-Life Hospital Example

Finance wants one trusted ‘Total Billed Revenue’ number and a ‘No-Show Rate %’ that appears identically on every report page, no matter which filters are applied.

Practical Exercise (Hands-On)

Build these measures: Total Patients = DISTINCTCOUNT(patients[patient_id]); Total Revenue = SUM(billing[amount]) (~$551K); Avg Treatment Cost = AVERAGE(treatments[cost]) (~$2,756); No-Show Rate = DIVIDE(CALCULATE(COUNTROWS(appointments), appointments[status]=”No-show”), COUNTROWS(appointments)) (~26%).

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. The overall functionality and steps should remain largely the same.

1. In Report view, right-click the patients table in the Data pane and choose New measure.
2. In the formula bar, type: Total Patients = DISTINCTCOUNT(patients[patient_id]) and press Enter.
3. Right-click the billing table, choose New measure, and enter: Total Revenue = SUM(billing[amount]). Format it as Currency.
4. Right-click treatments, New measure: Avg Treatment Cost = AVERAGE(treatments[cost]). Format as Currency.
5. Right-click appointments, New measure: No-Show Rate = DIVIDE(CALCULATE(COUNTROWS(appointments), appointments[status] = “No-show”), COUNTROWS(appointments)). Format as Percentage.
6. Right-click billing, New measure: Pending Revenue = CALCULATE(SUM(billing[amount]), billing[payment_status] = “Pending”). Format as Currency.
7. Right-click billing again, New measure: Failed Payments Count = CALCULATE(COUNTROWS(billing), billing[payment_status] = “Failed”).
8. Add a Card visual (or Multi-row card) and drag each new measure on, one per card, to confirm the values.
9. Save the file.

Expected Result

Total Patients = 50, Total Revenue ≈ $551,250, Avg Treatment Cost ≈ $2,756, No-Show Rate ≈ 26%.

Mini Lab / Scenario Task

Add two more measures: Pending Revenue (sum of amount where payment_status = “Pending”) and Failed Payments Count (count of bills where payment_status = “Failed”).