Multiple stacked columns in Pivot Chart... help?

[rogerdavid]

Ok so I think I'm up the creek on this one... ive got a uni assignment where I have a whole batch of data and I need to basically show planned vs actual tonnages mined, over time (Month = X axis). The tonnes need to be seperated into a stacked column that is proportioned by how many tonnes went to which destination. The tonnes come from a million different Block IDs.


So basically:
1. I want to create a chart where the Y axis is Tonnes Mined.
2. Then I need X axis to be MONTH
3. Within each MONTH there has to be two different stacked columns PLANNED and ACTUAL. The stacks represent how many tonnes went to each destination.
4. PLANNED and STACKED columsn need to be different colours, so as to be able to differentiate them in my report.

So it should look a bit like (bare with my ascii representation here)...

.......|...............|....................|...............|........
.......|...............|....................|...............|........
.......|...............|....................|...............|........
...PLANNED.....ACTUAL.....-...PLANNED.....ACTUAL.....-
^^^^^ JANUARY ^^^^^-^^^^^ FEBRUARY ^^^^^-

Here is what my data looks like
I have two sets of data from seperate databases: One database is "forecast" and the other set is "Actual"

Each set have equivalent columns

FORECAST
Block ID ......... Tonnes .........Month .... Destination
bah/24/5 ......... 10000 ......... Jan ........ Crusher
bob/63/7 ......... 99999 ......... Jan ........ Stockpile
cha/60/2 ......... 55888 ......... Oct ........Crusher


ACTUAL
Block ID ......... Tonnes .........Month .... Destination
bob/63/7 ......... 124351 ......... Oct ..... Stockpile
cha/60/2 ......... 935553 ......... Jan ...... Stockpile
bah/24/5 ......... 365667 ......... Jan ...... Stockpile


Notice that the Grade IDs have different positions in the rows, so I can't just paste one set of data next to each other. What's more, sometimes the ACTUAL is missing some Grade IDs, etc. Basically pasting the two data sets next to each other is out of the question.


Any idea how I'd attempt to set this up?

I've tried adding the data sets one under the other, then adding a single column with either "planned" or "forecast" next to the data.
This works to build the graph looking how I want when doing a Pivot Chart, however it considers Planned and Forecvast as part of the same TONNAGE series, and so wont let me change their colours.

Thoughts?