TABLEAU TUTORIAL: How to Adjust for Case Mix

Why Should I Adjust For Case Mix?

Even though the bottom hospital has higher unadjusted Radiology charges, the middle hospital has higher CMI  Adjusted Rad Charges

Even though the bottom hospital has higher unadjusted Radiology charges, the middle hospital has higher CMI Adjusted Rad Charges

With all of the data available to us, it is easier than ever to compare hospitals. It is also easier than ever to compare hospitals incorrectly and come to a wrong conclusion. The amount and cost of care provided to a single patient is related to how sick they are and these costs and outcomes flow up to the organization level.  Therefore you can’t accurately (or fairly) compare hospitals without knowing anything about the patients that the hospital admits.  For example,  Sentara Norfolk General Hospital is Norfolk VA’s only level 1 trauma center. On average, they see patients that require much more intensive (and expensive) care than Wellmont Lonesome Pine Hospital in beautiful Big Stone Gap Virginia. If you compared these two hospitals without adjusting you may find that on average,  mortality (a rate of in-hospital deaths) is higher in Norfolk General than compared with Lonesome Pine. Does the lower mortality rate at Lonesome Pine mean that the patient care is better there?  Probably not, it may just indicate that Norfolk General sees a higher percentage of patients with trauma injuries and heart failures, and these patients are just more sick.

One way to adjust for the complexity of care (and sicker patients) is to use Case Mix Index (CMI)in your calculation. Adjusting for Case Mix is one tool you can use to help make more accurate comparisons across hospitals that serve different patients or to compare hospitals to some benchmark (such as a state average). CMI is not perfect by any means, but using it helps make better comparisons.

How do I Adjust for Case Mix?

The Centers for Medicare and Medicaid Services calculate and publish lists of CMI for US acute care hospitals.  CMI appears to be available for download in different places on the CMS website (and for different years).  You can find 2015 CMI data here. If you need other years, just try searching the website.

To adjust for Case Mix, all you need to do is divide the rate by the CMI or the Transfer Adjusted CMI, whichever you are using.  Let’s pretend that Norfolk General’s unadjusted hospital mortality is 14%, then their adjusted mortality rate is 14%/CMI = 14/2.070 = 6.8%.  If Lonesome Pine’s unadjusted  hospital mortality is 14%, then their adjusted rate is 14/1.366 = 10.2% using 2015 CMI data.

Adjusting for Case Mix in Tableau

It is super-easy to adjust for CMI in Tableau.  The steps are:

  1. Download the data from CMS for the year you want, here is 2015 CMI data.
  2. Connect to the data in Tableau.Go to Data–>New Data Source–>text File, select the file, and Tableau will do the rest.
  3. Edit Tableau Relationships

    Edit Tableau Relationships

    Link it to your existing data. Go to Data–>Edit Relationships to join the CMS Provider ID (it is very un-descriptively called “Prov”  in my file) to the hospital ID identifier in your other data files.   Provider ID is also known as  “Medicare Provider Number”, “Provider number”, and MPN.

  4. Make sure your little red chain link is set so the join works tableau_red_chain_link
  5. Create a calculated field that divides a value of interest by the CMI or Transfer Adjusted CMI.


Dr. Jonathan P. DeShazo is an expert on health information technology, clinical data, and consumer informatics. He is currently an Assistant Professor in the Department of Health Adminstration and serves as Scientific Director of the Biomedical Informatics Core at Virginia Commonwealth University.

3 comments for “TABLEAU TUTORIAL: How to Adjust for Case Mix

  1. Brenda
    July 23, 2015 at 9:46 pm

    I like your posts.
    The scope and depth is exactly right.
    I am learning Tableau, and I live in a universe of health data.

    Please keep posting.

  2. jon.
    July 28, 2015 at 8:41 am

    I’m glad you like it, Brenda. Let me know if there is something you are particularly interested in and Ill try to post on it.

  3. Katrice
    September 30, 2016 at 3:19 pm

    I am still learning Tableau I need to create a report. The case mix weight for the final HIPPS/HHRG PER Medicare.

Leave a Reply

Your email address will not be published. Required fields are marked *