Drill down levels on pivot chart

[ranopano]

Hello, I am having trouble setting up a pivotchart and was hopin to get some input.

My data consists of the following:

• 1 column of US Regions (South, East, etc)
• 1 column of US State
• 1 column of State's population.

The chart is just a standard bar graph.

I want the chart to display the 4 US regions by default, then when a Region slicer is selected (South, East, etc), the chart zooms in on all of the states in that region.

Here are the problems I've been running into:

• If I include both the Region and State on the axis, all 50 states are displayed at once which is way too much information.
• if I remove the State field off the axis, I can't drill down and actually see what states are in that region. FOr instance if I select a Region/State slicer, it just will that single value.

How would I have it set up to display the 4 regions by default, then via slicer, be able to drill down where it shows all states in the selected region?

Hope this makes sense. if not let me know!

 

[ranopano]

Any help would be appreciated!
FYI I am working mainly with excel 16 and excel 2010