Output value based on a conditional count of other values

[Anonymous]

Hi,

I have a table that contains columns called Category, Sub-Category, Criteria and RAGB (Red, Amber, Green, Blue). RAGB is applied to the criteria and what I want to do is say that if there’s X Criteria with a RAGB of Red (within that Sub-Category) then output “Red”, if X with Amber output “Amber”, if X with Green output “Green” and finally when all the criteria are Blue output “Blue”. This will then be used to change the colour of the Sub-Category card on the report accordingly. I then want to do the same again but for the Sub-Categories to drive the colour of the Category card e.g. if X are Red output “Red” etc. etc. (again finishing with when they’re all Blue output “Blue” the same as for the criteria).

For clarity I have four different categories containing 8, 4, 11 & 8 sub-categories respectively (all with different names) and then each sub-category contains a different number of unique criteria.   

I’ve been scratching my head as to how to best achieve this (if at all!) can anyone suggest a solution please?

Many thanks.

[olgad]

a bit hard to imagine what you want , may be you can show a part of your data and then just on a snapshot color it the way you want. let me know, i do lots of custom formatting and coloring on the reports.

[Anonymous]

Thanks for the quick response and sorry the question wans't clear, does the below help? 

In hindsignt I'm thinking it might be better to apply a weighting to each of the RAGB colours to drive the sub-category and category results from. E.g. if a sub-category scores 0-10 it's green, 11-20 it's amber over 20 it's red and then the same for the overall category? 

The sub-category and category results are what I'm trying to generate from the data I have, I "think" I'm then ok with the report side using those values...