6 Replies Latest reply on Jun 21, 2016 8:30 AM by Dan Cory

    Tableau not picking up data's percentages

    heather.vogt

      Hello! I am looking to create several worksheets centered on census data, but Tableau isn't picking up the percents in my data. I grabbed the data off a county government's website and manipulated it to make it look more user-friendly. Upon trying to use the data in Tableau, however, percents aren't being picked up.

       

      Here's a screenshot of the data I'm trying to connect to in Tableau. The data lists percent of total values for a total population in the 8 congressional districts in MO who have a certain ancestral background.

       

      forum 1.png

       

      Here's what I am getting in Tableau when I connect.

       

      forum 2.png

       

       

      Here's when Data Interpreter is turned on:

       

      forum 3.png

       

      I think the problem lies in the random other information pulled in when I turn on Data Interpreter. These second and third "sheets" are the information I want in my viz. As you can see in the screenshot above, Tableau "sees" the data and it's listed in the Data Preview pane, but when I go to the worksheet for the data...

      forum 5.png

       

      ...here's what I get. As you can see, the percent of totals (the info I really want) and the total pop. (info I really want) aren't showing up as dimensions or measures.

      forum 4.png

      I'd like to create a viz in which the user can compare ancestry between congressional districts. Any ideas? I am totally new to this. I'm an instructional designer not a data person! Thanks!

        • 1. Re: Tableau not picking up data's percentages
          Dan Cory

          Heather - Thanks for your question!

           

          You are right about Tableau getting confused by the other data in your worksheet. With the data interpreter on, it's picking up the right values. You just need to set the number format of your columns to percentage.

           

          You also have a data layout problem. Tableau wants a format where each row describes a single fact. Spreadsheets frequently use other structures, unfortunately. Tableau's Pivot command can help you pivot the congressional districts into separate rows (instead of separate columns).

           

          The total populations will still cause you some problems though.

           

          Can you post the Excel worksheet here so we can have a look at it?

           

          Thanks,

          Dan

          1 of 1 people found this helpful
          • 2. Re: Tableau not picking up data's percentages
            heather.vogt

            Hello, Dan! Thanks for your response! I really appreciated your comment about how Tableau wants a format where each row is a single fact. I will finagle the data to make that work. I guess I'm afraid that manipulating the data like that won't make it authentic. I'm an instructional designer, so my aim is to make my course mirror real-life, authentic contexts as much as possible. However, if that's how Tableau interprets the data, that's how Tableau interprets the data. I will add a section about Tableau Pivot to make this data work.

             

            I attached the Excel document I'd like to use for this course below.

             

            Thanks for any and all help, Dan!

            Here's a cookie for your trouble: cookie.png

            • 3. Re: Tableau not picking up data's percentages
              Dan Cory

              Mmm..... cookies....

               

              I attached an Excel workbook with both the "original" and a version I cleaned up in Excel to make it more Tableau friendly.

              I also attached a Tableau workbook that uses both the original and the cleaned up version to build some simple visualizations.

               

              The original version can get somewhat cleaned up with the data interpreter and a pivot. But in Excel I was also able to put the "Total Population" in a more usual layout and add a row for the "Other" ancestry, since in most cases the percentages don't add to 100%. Both of these are pretty hard to do in Excel.

               

              Dan

              1 of 1 people found this helpful
              • 4. Re: Tableau not picking up data's percentages
                heather.vogt

                Thank you, Dan! Thank you! Thank you! Thank you!

                • 5. Re: Tableau not picking up data's percentages
                  heather.vogt

                  Dan, I just thought of this. I would like to include this information in my Introduction to Tableau course; however, I'd like to discuss how Tableau would interpret the data rather than the user having to change their data. You said Tableau would be able to pivot the data I bring in from Excel, right? Can I use my original Tableau data, the Excel sheet, for that? Again, I'd rather discuss a Tableau-centered solution rather than instructing users to change their data. I'm assuming pivoting the information in Tableau is best practices.

                   

                  What do you think?

                  • 6. Re: Tableau not picking up data's percentages
                    Dan Cory

                    You can use Tableau to pivot the original spreadsheet, as you can see in the tabs in my workbook that connect to the original spreadsheet. You won't be able to do much with the Total Population or account for the missing "Other" percentage.

                     

                    As for best practice, the advantage of doing things in Tableau is that it can update automatically if the Excel spreadsheet updates.

                    But there are many transformations that can't be done in Tableau (yet) and so Excel is frequently a good way to address those.

                     

                    Dan