I think if you want to use a drilled down dimension of state and cancer type then your data must be something like the following:
State, CancerType, Cancer%
AZ, Lung, 10%
AZ, Kidney, 20%
AZ, breast, 20%
AZ, liver, 2%
Then you can create a drilled down dimension of State and CancerType. And add this new drilldown as dimension in bar graph and Cancer% as measure.
I hope I got your question correctly.
First thing you need to do is to use crosstable load to get a cancer type as a field, not as a column:
KidneyRate as Kidney,
LingRate as Ling,
BreastRate as Breast,
LiverRate as Liver
You'll get a table with the Fields State, CancerType, Rate.
Next, create a master dimension, drill-down,including State and CancerType. Use this dimension in your charts.
Thank you Michael and Amuktha for your suggestions. I used the crosstable load as you've stated and it successfully created the CancerType bars after you drill down into the State. However, all the bars are the same for each state while some do not even appear under CancerType such as Missouri (screenshot below). What could be the reason for this as well as the fix? Thanks again!
Alright, I understand. However, I added new rates to another Excel sheet so I'm incorporating "CancerRate" instead. How can I make it so that it displays these new rates for the individual types of cancer? How can I link the "CancerRate" to CancerData?
UPDATE: I tried changed Rate in the cross table load to Cancer Rate, however, it drastically alters the statistics for the initial visualization.