Case study overview: turning claim denials into a managed metric
This Power BI case study builds a multi-page Medical Claims Denial Management dashboard that shows how much revenue is denied, why, by whom and where it can be recovered.
The business problem. Every denied claim delays or reduces payment. Revenue cycle teams often track denials in spreadsheets, so they cannot easily see which payers, procedures or denial reasons cost the most, or whether appeals are working.
The solution. A data model of claims, payers, procedures and diagnoses, with DAX measures for denial rate, denied amount and appeal outcomes. Report pages move from an executive summary to denial reasons, payers and procedures, so teams can focus on the causes that recover the most revenue.
Who it is for. Aspiring data analysts who want a standout healthcare portfolio project, Power BI developers improving their modelling and design skills, and revenue cycle and billing teams.
Business context: claims, codes and the revenue cycle
A medical claim is a request for payment that a provider sends to a payer, such as an insurer, after delivering care. Two code sets describe what the claim is for.
| Term | What it describes | Role in a claim |
|---|---|---|
| CPT code | The procedure or service performed | Tells the payer what to pay for |
| ICD-10 code | The diagnosis or condition | Tells the payer why the service was needed |
| Denial reason | Why the payer refused or reduced payment | Shows what to fix |
| Appeal | A request to reverse a denial | A route to recover revenue |
A payer can deny a claim when, for example, information is missing, the diagnosis does not support the procedure, authorization was not obtained, or the claim was filed late. Each denial adds rework, delays cash and, if never recovered, becomes lost revenue.
Why it matters for the revenue cycle. The revenue cycle runs from patient registration through coding, claim submission, payment and follow-up. Denial management sits at the end, but most denials start earlier, at registration, coding or documentation. A dashboard that links denials back to their cause lets teams fix the process, not just resubmit claims.
The data and data model
The source is a table of claims, with one row per claim or claim line. A typical layout includes a claim ID, service date, submission date, payer, provider or department, CPT code, ICD-10 code, billed amount, paid amount, claim status, denial reason and appeal status. [Adjust this list to the fields in your files, and state the source and number of claims.]
Preparation in Power Query
- Set data types, and standardize payer, department and denial reason names.
- Group detailed denial reasons into a manageable set of categories.
- Calculate a denied amount and a flag for denied claims.
- Check that billed, paid and denied amounts reconcile for each claim.
Star schema
| Table | Type | Typical contents |
|---|---|---|
| Claims | Fact | One row per claim, with keys, dates, billed, paid and denied amounts, status |
| Payer | Dimension | Payer name, payer type |
| Procedure | Dimension | CPT code, description, category |
| Diagnosis | Dimension | ICD-10 code, description, category |
| Denial Reason | Dimension | Reason, reason category |
| Date | Dimension | Date, month, quarter, year, marked as the date table |
If a claim can carry several codes, keep one row per claim line, so each CPT code and its amount stay together. Use synthetic or anonymized data, and keep patient identifiers out of the report.
Key DAX measures
Table and column names are examples, so match them to your model.
Total Claims = COUNTROWS ( Claims )
Denied Claims =
CALCULATE ( [Total Claims], Claims[ClaimStatus] = "Denied" )
Denial Rate % = DIVIDE ( [Denied Claims], [Total Claims] )
Total Billed = SUM ( Claims[BilledAmount] )
Total Paid = SUM ( Claims[PaidAmount] )
Denied Amount =
CALCULATE ( SUM ( Claims[BilledAmount] ), Claims[ClaimStatus] = "Denied" )
Denied Amount % = DIVIDE ( [Denied Amount], [Total Billed] )
Appealed Claims =
CALCULATE ( [Denied Claims], Claims[AppealStatus] <> "None" )
Appeal Rate % = DIVIDE ( [Appealed Claims], [Denied Claims] )
Appeal Success % =
DIVIDE (
CALCULATE ( [Denied Claims], Claims[AppealStatus] = "Overturned" ),
[Appealed Claims]
)
Avg Days to Payment =
AVERAGEX (
FILTER ( Claims, NOT ISBLANK ( Claims[PaymentDate] ) ),
DATEDIFF ( Claims[SubmissionDate], Claims[PaymentDate], DAY )
)
Denial Rate Prior Year =
CALCULATE ( [Denial Rate %], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )The status values and appeal labels are placeholders, so use the ones in your data. Calculate rates from counts or totals with DIVIDE, never by averaging row-level rates. Keep partial denials in mind: if a claim is paid in part, decide whether it counts as denied, and apply that rule consistently.
Multi-page report walkthrough
| Page | Question answered | Main visuals |
|---|---|---|
| Executive summary | How much revenue is denied, and is it improving? | KPI cards for claims, denial rate, denied amount and appeal success; monthly denial rate trend |
| Denial reasons | Why are claims denied? | Ranked bars of denied amount by reason category, decomposition tree |
| Payers | Which payers deny the most? | Denial rate and denied amount by payer, matrix of payer by reason |
| Procedures and diagnoses | Which CPT and ICD-10 codes are denied most? | Top CPT codes by denied amount, top ICD-10 categories, matrix of CPT by reason |
| Appeals and recovery | Are appeals working? | Appeal rate and success by payer and reason, amount recovered |
Design choices that help readers
- Rank by denied amount as well as denial rate, since a low-rate payer with high volume can cost more.
- Use a Top N filter to keep code lists readable.
- Slicers for date, payer, department and reason are synced across pages.
- Drill-through from a payer or reason opens the claim-level detail, so billing teams can work the denials.
Explore the working report in the live dashboard.
Insights, business value and lessons learned
What the dashboard reveals
- [Headline: give total claims, denial rate and denied amount for your selected period.]
- [Reasons: name the top two denial reason categories and their share of denied amount.]
- [Payers: name the payer with the highest denial rate and the one with the highest denied amount.]
- [Codes: name the CPT code or procedure group with the highest denied amount.]
- [Appeals: give the appeal rate and appeal success rate, and the amount recovered.]
Replace each prompt with the figure shown in your live dashboard.
Business value
- Revenue cycle teams can target the denial reasons and payers that cost the most.
- Tracing denials to registration, coding or documentation points to process fixes.
- Appeal tracking shows where effort recovers revenue and where it does not.
Lessons for Power BI builders
- Learn the business terms first, because the measures depend on them.
- Define what counts as a denial, including partial denials, before writing any measure.
- Show denial rate beside denied amount, since each tells a different story.
- Group denial reasons, or the reasons chart will be unreadable.
- Validate measures against a hand-counted sample before publishing.
Frequently asked questions
What is claims denial management? It is the process of tracking, analyzing and resolving claims that payers refuse or reduce, so that preventable denials are fixed at their cause and recoverable ones are appealed.
What is the difference between CPT and ICD-10 codes? CPT codes describe the procedure or service performed. ICD-10 codes describe the diagnosis or condition that justified it.
How do you calculate denial rate? Divide the number of denied claims by the total number of claims. Many teams also track the denied amount as a share of billed amount.
What KPIs belong on a denial management dashboard? Common KPIs are denial rate, denied amount, top denial reasons, denials by payer, appeal rate, appeal success rate and days to payment.
Is this a good Power BI portfolio project? Yes. It combines business context, data modelling, DAX and report design on a problem every healthcare organization faces.
Try it yourself
Explore the live dashboard, then follow the step-by-step tutorial and use the complete data and related files to build it yourself. Need a revenue cycle or claims analytics dashboard for your organization? Contact datascientist.ca.