Linking data!

[#1]

Hi,

I'm struggling with a data linking problem and could use some pointers. I have two scatterplots:

Scatterplot 1 (High Level Category)                    Scatterplot 2 (Sub-Category)

Marketing                                                              Commercial Print                               

IT                                                                         Software Applications

Infrastructure                                                         Building Expenses

Note that the sub-categories are sub-groups of the high level categories. The x and y axes are the same.

I have gotten to the point where if I filter by the sub-category, only the applicable high level category appears in the first chart. i.e, if I choose Software Applications as the filter, the second chart shows Software Applications and the first chart only shows IT. However, I also want the reverse to work. Therefore, when I filter by the high level category, ex: Marketing, that only the Commercial Print data point shows up on the second scatterplot. If all filters are clear, then all sub-categories should show up.

Any ideas?! I've been reading about data linking and concatenate functions but I'm not sure if/how they apply here.

Thanks so much!

[rustyfishbones]

Try this

=IF(RANK(SUM(Spend),[Number of Vendors])<=4,SUM(Spend))/SUM(TOTAL Spend)

[#3]

Thanks! I ended up using your trick on data linking and the concentration ratio etc ended up working itself out when I created linked data!