Tableau Tutorial: Compare a Hospital to State and National References

There are two easy ways to compare a row (in this case a hospital) to the average or some other reference in Tableau. 1) Create a reference line (for charts like bar graphs), or 2) Create a calculated field you can use in any type chart.

Data Source:  Hospital Compare Health Data

The data we are using for this example is Hospital Compare Outcomes data from CMS. You can find a description and simple download of the data here.

Create a Reference Line in Tableausidebyside_mort

By far the simplest way to compare a row to the average of all rows in Tableau is to use a reference line.  In our example, we will be comparing ahospital outcome measure to the average of that outcome measure across the entire table(country), and then across the entire pane (which we will make at the state level).

If we have a simple side-by-side (vertical) bar chart like this one with Hospitals and States on one axis and Average 30-day Mortality rates from Heart Attack on the other axis. If you want to see which hospitals are higher or lower than the national average, you can simply add a reference line.

add_referenceRight click on the vertical axis and select “Add Reference Line, Band, or Box.” You can then choose between lines, bands and other types of high-low indicators.  You can also choose between different aggregate functions like Average, high, low, etc.   You should probably also change the label while you are at it.  But the most important thing for you do select is the Scope.  If you choose, Table, it will give you the average across the country (all records). If you choose Panel, it will give you the average for each state, provided that you have your hospitals grouped by state as shown above.

Easy huh?

 

 

Create a calculated field reference that you can use in any type chart in Tableau

This way is more versatile because you can use it in all sorts of charts and tables. However, it requires you to make a calculated field and keep in mind how it is aggregated. Just like the example above, we will create a reference calculated field for “Average 30-day Mortality rates from Heart Attack”. We will call it “Reference Average 30-dayreference_calc_field Mortality rates from Heart Attack “. Open the Create Calculated Field dialog , give it a name, and enter in a similar calculation.  You calculation will use the AVG function and the TOTAL function like this: TOTAL( AVG([Hospital 30-Day Death (Mortality) Rates from Heart Attack] )) .  Pretty easy so far.

Now in the chart, you will use the calculated measure just like any other measure, but with one extra consideration.  Once you put the measure in your chart, You have to specify the level that you want to aggregate function to calculate. reference_compute If you specify “table” it will give you national level hospital averages. If you specify pane, it follows the grouping you have. If you pick State, you know what it will average by. And so on.

jon.

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.

Leave a Reply

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