11 Replies Latest reply on Jan 2, 2019 3:54 PM by Charles Hannon

    Help with Dashboard

    Charles Hannon

      I am learning about Dashboards. The attached workbook is based on CDC data for stroke mortality. I've created a dashboard that shows a map of US and you can use the map as a filter to select a state and then get a graph for that state that shows data by gender and another graph that shows a race/ethnicity breakdown for the selected state. But when no state is selected, the two additional graphs show data for all states, one state at a time. How can I make it so that when the user is looking at the whole map, with no particular state selected, the gender and race/ethnicity graphs show aggregated US data, not (scrolling) data for each state individually? In other words, the gender graph should just show the averages for all men, women, and overall. And likewise the race/ethnicity graph. Thanks.

        • 1. Re: Help with Dashboard
          Ken Flerlage

          Just remove State or Geo Level from the Column/Row shelf on the two charts. This will force them to aggregate at an overall level. Since you're using filter actions, these charts will filter by state when you click on the map and should give you what you're looking for.

          1 of 1 people found this helpful
          • 2. Re: Help with Dashboard
            Charles Hannon

            Thanks, I appreciate your help. When I do that though, the numbers for the view with no state selected are a sum rather than an average. In other words, with OH selected as an example, the overall gender rate (per 100,000) is 78.4 which should be correct. But with no state selected the rate is 4,337.4 which is not correct. It needs to be the average for all US. Perhaps I've done my calculated fields poorly?

            • 3. Re: Help with Dashboard
              Ken Flerlage

              What exactly is the calculated field supposed to be doing?

              • 4. Re: Help with Dashboard
                Charles Hannon

                Overall by State (used in the Map) gets Data Value for all genders and all race/ethnicities.

                Overall by State with Gender gets Data Value for all race/ethnicities but separates gender by overall, male, and female.

                Choose Race/Ethnicity gets Data Value for all genders but separates race/ethnicity by overall, black, white, etc.

                • 5. Re: Help with Dashboard
                  Ken Flerlage

                  I don't think you need the Choose Race/Ethnicity, Overall by State with Gender, and Overall by State calculated fields. We can use filters on the sheets instead. Here are my recommendations (assuming I understand what your goal is)

                   

                  On the Overall Map Sheet

                  1) Add the following filters:

                  • GeographicLevel=State
                  • Gender=Overall
                  • Race/Ethnicity=Overall

                  2) Change the color to use AVG(Data Value).

                  3) Delete the Overall by State calculated field.

                   

                  On the Gender Sheet

                  1) Add the following filters:

                  • GeographicLevel=State
                  • Race/Ethnicity=Overall

                  2) Change the measure (on the rows shelf) to use AVG(Data Value).

                  3) Delete the Overall by State with Gender calculated field.

                  4) Remove State or Geo Level from the columns shelf.

                   

                  On the Race/Ethnicity Sheet

                  1) Add the following filters:

                  • GeographicLevel=State
                  • Gender=Overall

                  2) Change the measure (on the columns shelf) to use AVG(Data Value).

                  3) Delete the Choose Race/Ethnicity calculated field.

                  4) Remove State or Geo Level from the rows shelf.

                  Now, since we're using AVG on the measures, I think that should give you what you need both when you click on a state and when you are not selecting any state at all.

                   

                  With State:

                   

                  Without State:

                   

                  See attached workbook.

                  1 of 1 people found this helpful
                  • 6. Re: Help with Dashboard
                    Charles Hannon

                    Thanks so much! I guess my problem started with using calculated fields to filter data instead of the filter feature. I appreciate your help.

                    • 7. Re: Help with Dashboard
                      Ken Flerlage

                      No problem. Glad this helped!

                      • 8. Re: Help with Dashboard
                        Charles Hannon

                        Ken,

                        I wonder if you would help with one more question. I wanted to add one more viz to the Dashboard--showing the 5 counties in each state with the highest stroke mortality rate. When no state is selected, that graph should show the top five counties in the whole country. In the attached workbook, I have two tries. In Dashboard, the viz uses the Top N feature of a filter. That works when no state is selected, but when a state is selected, the graph only shows data if the state has a county in the top 5 in the country (TX and VA and CA for example). I tried adding the filter to context but I'm not doing it right. In Dashboard (2) i tried creating the viz using Index() and that works for individual states but I can't see how to get it to work the way I want when no state is selected.

                         

                        One complicating matter for the first solution is that some states have counties with the same name, so I am using both GeographicLevel:County and Location ID to separate them out.

                        I hope this is clear.

                        • 9. Re: Help with Dashboard
                          Ken Flerlage

                          The Top N Filter is the probably the best approach, but you're running into an Order of Operations problem. Take a look at the visual below from Tableau's Order of Operations - Tableau:

                          Your action filter is a Dimension Filter, but that type of filter occurs after Top N filters. So your Top N filter is being applied first, then your action filter. We want to reverse that, so add your action filter to context, making it a Context Filter, which will be applied before Top N filters.

                          • 10. Re: Help with Dashboard
                            Ken Flerlage

                            One other unrelated thought while I'm here. People are really going to struggle from a UX standpoint to click on small states such as Rhode Island. Have you considered using a hex map instead? My friend, Matthew Chambers has a brilliant blog on this and it's pretty easy to do. Check it out and give some thought to it: https://www.sirvizalot.com/2015/11/hex-tile-maps-in-tableau.html

                            • 11. Re: Help with Dashboard
                              Charles Hannon

                              Ken,

                              Thanks again very much. Your solutions make sense (always seems easy in retrospect!). Now that I understand more about how dashboards work I will spend time tomorrow learning how to male the map viz a hex map instead. I've been seeing those for a couple years now and always wondered, "I wonder if Tableau can do that?" Of course the answer is usually Yes, with a little behind the scenes magic!

                               

                              Thanks again for all your help.