You should be able to add both data connections, 1st your cube and then your spreadsheet.
With your worksheet make sure the Cube dataset is selected. Then right click on your spreadsheet and select relationships. Use this window to match the City column from the cube to the city column in the excel.
Then, if you want to use the built in geographic features in Tableau, right click on the state field and set the geographic properties to Zip or State.
You can then simply drop your fields on your worksheet and build your viz.
I did create a relationship using the relevant command in the Data menu - but when I drop Population from the cube and the State from the spreadsheet I get an error that says - cannot blend aggregated data...because there are no linking fields in the view".
I would want a bar chart in this case because I want to show the overall average also.
Ahh... yes. you have to put the field that you link the sheets on, on the view first. In this case, if the relationship between them is on the City column, you have to put the City measure on the page first. Then add the others.
This may give you other problems with levels of detail etc, but do this first and see how you go.
I did do that - but then the population was not aggregated at the state level. Just got the city level data grouped by states.
Yeah... not sure if you can get around that one. Will have to have the City somewhere to link it, so what if you put the State, then City on the Row Shelf. Then put a subtotal on the State level. If the subtotal returns the right values, you could use a "hide trick" so that only the subtotal shows.
HideTrick: Create a calculated field which simply returns one value (or use a measure that doens't change in your data). Add it to your row shelf, after the State, then right click on it in the pane and select hide. This will hide the data from the view but keep it there for the totals/subtotals. If you subtotal at state, this should work for a table. Not sure about geographic representation??