I have a complex one so forgive me if this isn't clear - if absolutely necessary I'll create an example but I just don't have time at the moment.
I have data in a table which represents a Risk Matrix:
|Unlikely - Rarer than once every 15 years||Critical area||255||75|
|Severity 1||Rare - Once between every 15 years and 5 years||Critical area||255||150|
|Severity 1||Likely - Once between every 5 years and 2 years||Critical area||255||225|
|Severity 1||Highly - Once between every 2 years and once a year||Critical area||255||300|
|Severity 1||As good as certain - Multiple every year||Critical area||255||375|
|Severity 2||Unlikely - Rarer than once every 15 years||Serious area||65535||25|
|Severity 2||Rare - Once between every 15 years and 5 years||Serious area||65535||50|
|Severity 2||Likely - Once between every 5 years and 2 years||Critical area||255||75|
|Severity 2||Highly - Once between every 2 years and once a year||Critical area||255||100|
|Severity 2||As good as certain - Multiple every year||Critical area||255||125|
|Severity 3||Unlikely - Rarer than once every 15 years||Less serious area||65280||10|
|Severity 3||Rare - Once between every 15 years and 5 years||Serious area||65535||20|
|Severity 3||Likely - Once between every 5 years and 2 years||Serious area||65535||30|
|Severity 3||Highly - Once between every 2 years and once a year||Serious area||65535||40|
|Severity 3||As good as certain - Multiple every year||Serious area||65535||50|
|Severity 4||Unlikely - Rarer than once every 15 years||Less serious area||65280||5|
|Severity 4||Rare - Once between every 15 years and 5 years||Less serious area||65280||10|
|Severity 4||Likely - Once between every 5 years and 2 years||Less serious area||65280||15|
|Severity 4||Highly - Once between every 2 years and once a year||Serious area||65535||20|
|Severity 4||As good as certain - Multiple every year||Serious area||65535||25|
|Severity 5||Unlikely - Rarer than once every 15 years||Less serious area||65280||1|
|Severity 5||Rare - Once between every 15 years and 5 years||Less serious area||65280||2|
|Severity 5||Likely - Once between every 5 years and 2 years||Less serious area||65280||3|
|Severity 5||Highly - Once between every 2 years and once a year||Less serious area||65280||4|
|Severity 5||As good as certain - Multiple every year||Less serious area||65280||5|
By creating a Pivot Chart from this data I can create a 5x5 table, containing (for arguement's sake) "WEIGHT" data with each Cell the appropriate COLOUR:
My issue is that, to create this Matrix, I cannot link the data (above) to my base data (ie: holding Dates and Events) - as if a Selection is made which does not include one of the 5 SEVERITIES or 5 LIKELIHOODS then I cannot create the full Matrix. I can obviously select the "Show all Values" and deselect "Supress When Value is Null" Chart options - but then I get extra "blank" rows and columns - plus I then no longer have access to all Cell COLOURS, eg:
Of course this means that (if I make the data above a "data island") when I attempt to COUNT the number of Events for each Cell - ALL data is returned, regardless of the current Selection.
I am currently toying with the idea of a hybrid of the two implementations where I use a "data island" version of the table for the 'background' (with COLOURS) and then a transparent 'foreground' Pivot Chart using a linked table for the currently Selected data... however, I would like to show Totals (without having the "blank" rows and columns shown above - which, by viewing the table data highlighted above, I can understand why these are present).
Am I missing some fundamental data concept?!
How can I create an unrelated Pivot Chart which displays linked data? (or how can I remove the "blank" rows and colums from my Pivot Chart?).
Any help will be greatly appreciated!