Help with Street light gauge

[polingolingo]

Good afternoon,

I have been searching my question in the boards without luck.  Please forgive me if similar has been asked before.

I have sample data that looks like the below. I have four total deals and the deals are made up of different categories. the sum of all the categories make up my total margin.   In the example below, Deal1 made a total of -40. 

Q1 - What I want to create is a street light gauge with green and red lights & next to the lights, I would have the count of deals that either made money (green) or lost money (red). In the case of the example data below, I would have a gauge light that shows Green = 1 & Red = 3 (as three deals lost money & 1 made money) for a total of 4 deals.   

Q2.  Side question,  I want to have an object box that returns the total margin results on a specific category when a deal is selected. If I just want to see the "payables" total margin, and I select Deal1 - I would get the result of =40. But I do not want to have "payables" as a selected field. The only selected field would be deal 1.  

 

Thank you in advance for your help and ideas 

[polingolingo]

Ruben, 

Maybe I was just being confusing with the traffic lights.  What I am really looking for is a conditional count function based on that original straight table in which I get a "count" of results based on whether the deal made or lost money.  So, what I need is a count function that returns = 3 for those deals that lost money and = 1 for the one deal that made money.  

Thank you 

[rubenmarin · Accepted solution]

Another try

[polingolingo]

Perfect! thank you so much for all of your help Ruben.  

[rubenmarin]

Sorry for the confusing answers. If it's not clear what I have done:

- I used valuelist dimension to fix 2 rows. 

- Check the expression for each light using pick(Match()) of the value list

- There is an expression that counts reds and other for greens (zero result is counted as green because the >=0)

- In presentation tab hide the dimension and table headers