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.
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.
Right 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.
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-day 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. 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.
Latest posts by jon. (see all)
- How to Make a Dual Axis Tableau Map Showing Hospital Zip and Patient Zip - September 22, 2017
- Is Healthcare Management a Large Portion of Costs? - June 28, 2017
- The Sickest 5% of Americans Use 50% of All Healthcare Spending - May 26, 2017