I have data for team registrations - each team is a unique unit. The location data includes a state, county, and zip code. I have assigned counties to one of 8 regions and I have a zip code lookup table that I use to place a team in the proper county and region. In sharing this data for discussion, I need to look at summarized data by county and by region (and eventually by school district). I can get a basic bar chart by Region to show where the teams are located. Maps are proving to be more challenging.
Issue #1: With the raw team data, my filled map only showed data for counties that included teams. I tried some blending on the Zip/Region lookup table – first making the Zip/Region table the main source and blending on the team registration and then the registration data as the main source blended with the Zip/Region table, but I could not get a basic filled map with all of the counties showing –I would actually like to highlight those counties with no teams in them as opposed to showing only where the teams are.
To solve this problem I created a separate table in Excel that had the team count for each county and included all counties with a 0 for those with no teams. This frustrated me because I feel like I am providing all of the necessary data with the individual registrations and the related information from the Zip/Region table, but ultimately this table let me build the first map that I needed – all counties filled with a marker showing how many teams in that county. At this point, it is not necessarily the cleanest way to show this data – but has the numbers I want to see.
Question #1: Is there a way to take my original registration data with the Zip/Region information and create a filled map without having to create the second table of summary information?
Issue #2: As a companion chart, I would like to have a filled map showing the 8 regions with a single marker showing how many teams are in that entire region. I can get the calculation of number of teams in a region (called Teams by Region) – but not a marker showing it only one time for the region – it shows on every county.
Explanation: Each county is assigned to a particular region, so the County/Region information is found in the Team Data table, the Zip/Region lookup table and the Team Count Summary table – as I was not having good success with blending, so I looked up the relevant values and put them in the tables where I thought the information would help the calculations. In addition to the counties belonging to a particular region, I have assigned a variable called Rollup County, so that for each region, there is a single county within that region where I would like to place the marker showing summary information for that region. I haven't figured out how this will help me though.
Question #2: How can I get a filled map with shading for each region and a single marker showing the total number of teams in that region?
Note: For now, a single value is sufficient for each region. With other data in my organization but not included here, the teams are categorized into 4 types and I have been trying to create this same chart with a pie (I know many people hate them), showing the categorized teams in a pie for each region with the pie larger based on number of teams, and the pie showing the breakdown of the four categories. I have tabled that version for a while in order to learn more basic information with the filled maps and their capabilities.
I have included a workbook with the three tables mentioned in my questions. Thank you for any guidance in how to create these sheets. And if you think of a better way to visually share this information other than what I have requested – please share your ideas.
I am using the latest version of Tableau Desktop – 9.2 and I would consider myself an advanced beginner with using and understanding Tableau.
TeamDataShared-v9.2.twbx 208.7 KB