This post includes an interactive data visualization, which is best viewed on a computer or tablet. However, if you must use your phone, at least turn it landscape.

Observe

First, just observe the visualization.

The color scale shows the excess or shortfall to support a family of four on a single income across nearly U.S. 400 Metropolitan Statistical Areas (MSAs).

I calculate this excess/shortfall by subtracting the local poverty threshold for each MSA and "Money for surprise expenses" from the gross income in that MSA at the selected percentile.

I've used a red/green diverging color scale that is set to automatically recenter at zero as variables are changed in the visualization.

So, if a county is red that means a family of four on a single income is in poverty (after adjusting for "rainy day" expenses). The redder the MSA, the more impoverished that family would be.

And then vice versa for green MSAs.

Hover

Next, hover over any MSA to bring up a tooltip showing the math I just described above. For the chosen MSA, the tooltip will show:

1. The annual income at the selected percentile
2. The poverty threshold (accounting for cost of living)
3. Additional money for surprise expenses
4. The excess or shortfall to support a family of four on one income

Filter

There are two filters you can use.

First, use the "Percentile" filter to choose between different percentile incomes:

• 10th
• 25th
• 50th (median)
• 75th
• 90th

Next, use the slider in the "Money for surprise expenses" box to see what happens to the map under different scenarios.

This last function is really what gives this visualization power. It shows us how far people are from poverty across the country.

Zoom

Since some MSAs span two or even three states, I couldn't add a state filter to this visualization to zoom in on different cities.

But you can zoom yourself. Simply hover anywhere on the map. Navigation buttons will pop up on the left side of the map. Click on the arrow and then click the first icon, which is called "zoom area." Then you can drag a box around an area and Tableau will zoom for you. To go back to the entire contiguous U.S., click the house icon in the navigation panel.

Prepare the OEWS data

1. Download the May 2024 Occupational Employment and Wage Statistics data table. I used the "All data" XLSX file.
2. Scrub the file of all blank cells and extraneous data not needed for this visualization. The only fields I kept were Area, Area_Title, Area_Type, Occ_Code, Tot_Emp, and all the Percentile incomes. Only keep data with Area_Type = 4 or 6 and with Occ_Code = "00-0000." Save cleaned file as a csv or txt file.
3. Bring file into whatever database cleaning program you use (I used Tableau Prep). Create a calculated field on Area to count length of the string. Only keep Area numbers with 5 digit length. (Note, you could have accomplished this by only keeping Area_Type = 4, but I want this data for future use. Area_Type = 6 are the nonmetropolitan areas... which were not part of my initial scope for this tool, but will eventually get added to the viz.)

Prepare the SPM Thresholds by Metro Area data

1. Download the "SPM Thresholds by Metro Area: 2024" excel file.
2. Scrub the file of all blank rows to get it readable by database cleaning software.
3. Note that the Area code (CBSA) is in this database. This is what you need to join to the OEWS data. I chose to use the "Renters" column as the poverty threshold.

Join SPM and OEWS data

1. Join the two data tables on Area code (CBSA).
2. Pay attention to the number of rows dropped from the OEWS database. I found that it was roughly ~150 MSAs missing from the join. This is a problem that you must manually fix. So roll up your sleeves and start matching the missing MSAs by Area_name. Note that the area names won't always be exactly the same, but they will be close enough. For any rows in the OEWS data table that have no match at all in the SPM table, I used one of the broad state metro or nonmetro area estimates as a proxy. For example, when I couldn't find "Olympia-Lacey-Tumwater, WA," I used "Washington metro" as a proxy.

Finalize database

1. I chose to join one other reference table to make sure the map automatically scaled to the contiguous U.S. states. If you choose to do this, find a CBSA to State reference table and join that in. Then in the next step, just filter out AK and HI. For those of you who live in AK and HI, I plan to add your data in soon. I just wanted to get this first cut published ASAP so used this as a shortcut. My apologies.
2. The database should be complete. Generate output and get ready to feed it to Tableau.

Create Tableau visualization

I'm assuming you know the basics of Tableau in these instructions

1. Connect Tableau desktop to the finished database
2. Create a parameter called Percentile. Enter values "10th, 25th, 50th, 75th, 90th."
3. Create a parameter called "Money for surprise expenses." Set it up as a float with a min = 0, max = 25,000 (or whatever you want), and step size = 500
4. Create this calculated field, which will become your color scale:
   
   case [Percentile]
   when '10th' then [10th Percentile] - [SPM renters] - [Money for surprise expenses]
   when '25th' then [25th Percentile] - [SPM renters] - [Money for surprise expenses]
   when '50th' then [Median] - [SPM renters] - [Money for surprise expenses]
   when '75th' then [75th Percentile] - [SPM renters] - [Money for surprise expenses]
   when '90th' then [90th Percentile] - [SPM renters] - [Money for surprise expenses]
   END
5. Make sure Area is set to a CBSA/MSA geographic data type
6. Drag Latitude to rows and Longitude to columns
7. Drag the calculated field you create in step 4 to the color card
8. Filter out AK and HI (for now)
9. Add the worksheet to a dashboard along with the two parameters you created, then add some flair! Add your logo, change color scheme... make it yours!