The reason you are having problems is that all your dimensions and measures are unique - they don't relate to one another -
Think of an excel sheet (a cell driven application) that has a separate column for each measure and each dimension - and some one asks what are the top and bottom 3 states - the answer is look in columns B through G
Tableau is a data base driven application where you are working with a an entire dimension at a time - you are trying to plot 6 dimensions at once but they are mutually exclusive
To correct that you can pivot the state 1 - state 6 columns around the rest of the data
open the data source
select the first state column and shift select the last column to highlight all six
right click a header and select pivot
you will have 2 columns - I changed the measure to "State Rank" (it will be your former header) and the Dimension to "6 State"
6-State will show up as string field - convert it to a geo filed by right clicking and select geographic property then select state/province
Now drag it to the canvas and it will create the view below
Now all that said - you still have issues with the measures - again - each is an individual measure that relates to you state 1-6 original ranking -
the data structure would be better suited
and so on -
I know that is not what you wanted to hear - but you may be able to do some of the restructuring pivoting in excel or with a custom SQL
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
map.twbx 91.9 KB
Thanks for your prompt response. Attempting to follow what you mentioned but still getting a little confused. Have attached to screen shots. Believe I got through the "right click a header and select pivot" if the attached pivot.jpg is correct? However, what I have on the canvass is not right (see pivots_on_canvas.jpg). I have done pivot charts before but only in Excel. Maybe I messed up one of your steps. You will need to be very deliberate with me. Still learning Tableau plus maybe with heat wave here in the SF Bay Area, well harder to think correctly.
Think I at least got to emulate your first geo map showing the six states (to 3 at 10% income level and to 3 at bottom 90% level), filtered for year 2012. The twbx file is attached.
However, still not clear on the values (and really on the map only want to show the value with each state, for example, if Click on New York would see $720,000, Colorado, $705,000 etc. So not clear what you mean on the data view for values. To do this do I repeat for the values (as state) and add it to the pivot table?
showing_six_states.twbx 75.4 KB
I tried to explain this in my first post - the issue is with how your data is structured - Pivoted or unpivoted your data needs to have a tie between the states that you want to plot and the values for the measure you want to plot -
your data (unpivoted) is has a single record for 2012 - (see attached an the broken image below - in that record in separate fields you have states 1 - 6 listed and the 6 separate fields you have the value of the 10% invome level
Your data needs to be structured like this -
In what I showed you before only pivoted the the 6 states to give you an example of how the data needs to be structured - you will not be able to get the all the individual data fields pivoted into a good data structure inside Tableau
you will need to do it either in excel or with a custom SQL before you bring it into tableau
I know it is not what you want to hear but you will need to restructure the data to get what you want
Larry's 2012 data.xlsx 13.8 KB
Happy Labor Day. I think I am largely there now and data shows the same groups, multiple years with all six states. I have attached a workbook. Issue becomes with multiple year filters, in this case 2011 and 2012. First here is actual base data again:
Top 10% top incomes:
2012 New York, $720,000; Colorado $705,000; California $702,000
2011 New York $700,000; California $675,000; Colorado $650,000
Bottom 90% top incomes:
2012 Delaware $66,000; North Dakota $64,000; Vermont $61,500
2011 Florida $65,000; Ohio $62,000; Minnesota $61,500
For top three states:
Click on New York, shows State 1, $720,000 (based on 2012) CORRECT
Click on Colorado, shows State 2, $705,000 (based on 2012) CORRECT
Click on California, shows State 2, $675,000 (based on 2011) NOT CORRECT, as should show State 3, $702,000
For bottom three states:
Click on Delaware, shows State 4, $66,000 (based on 2012) CORRECT
Click on North Dakota, shows State 5, $64,000 (based on 2012) CORRECT
Click on Vermont, shows State 6, $61,500 (based on 2012) CORRECT
Click on Minnesota, shows State 6, $61,500 (based on 2011) CORRECT
However, both states Ohio and Florida are showing and SHOULD NOT be showing (Ohio would be behind North Dakota as State 5 and Florida behind Delaware as State 1).
I don't know if an additional counter is needed? I did use calculated fields successfully to pull the correct income amounts. Again, the workbook is attached.
Thanks goodness the San Francisco Bay Area is cooling off!
Geo_Map_to_use_ver3.twbx 66.5 KB