Icon color update based on multiple KPI metrics

I have to create a summary page in which I have Icons in a text area which are either Red, Yellow or Green in color based on the performance of the KPIs based on the detailed performance (KPI charts) shown on other pages.

On the detailed level pages I have created a similar icon in text areas for individual charts. (For e.g. for one chart there is an icon in a text area which is either red, yellow or green based on a defined thershold for that chart, I have defined rules for each color)

What I want to do is to update the color of summary page Icons based on the number of  green, yellow or red Icons in a detailed page. For e,g, in the detailed page named as Cost Charts, I have 3 charts each having its own performance icon indicator defined based on its own thersholds. For instance for one chart the performance Icon metric is  If Sum(Amount) > 10000 then Green, If Sum(Amount) between 5000 and 9999 then yellow, if Sum(Amount) < 5000 then Red. So if the Sum(amount) > 10000 then the icon for that chart will turn green. I want to show an Icon in text area on summary page for Cost Charts with Icon color changing based on the following criteria: 0 red Cost Charts then Icon color green, 1 red Cost Chart then Icon color yellow, > 1 red Cost charts then Icon color red.

Any suggestions on how this can be done? 

I have attached a spotfire file and sample data. In the summary page of the spotfire I have also mentioned the criteria for the summary page icons. Thanks for the help!


File sourcefile.xlsx10.7 KB
Binary Data summarydashboard.dxp144.83 KB

(1) Answer