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 

[rubenmarin]

Hi, for Q1 you can use an expression like: If(Sum(TOTAL <Name> [total margin])<0, 0, 1)

and configure the lights based on that returned values.

For Q2 you can use set analysis to fix 'payables' as Category:

Sum({<Category={'payables'}>} [total margin])

[polingolingo]

Good morning, 

Thank you for your reply.  for Q2 I am clear - exactly what I needed.

For Q1 - I was more looking for something like the below -  Where the Green & Red shows total distinct count depending on whether the deal made or lost money.   

Thank you again for your time 

[rubenmarin]

Hi, that's how the sample works, have you checked it? It'sa uploaded as attachment in the last post.
I configured it to show 0 as red and 1 as green, and the expression returns 0 (red) for deals with negative nargins and 1(green) for deals with positive margin

[polingolingo]

I carefully reviewed your sample.  Obviously,my limited knowledge on gauges its becoming apparent. what I see in the sample is either a red and a green next to deal name -  But what I want is 1 traffic light that returns a total count.  How can I get there from what you've initially built? 

Thank you  again for your time on this. It helps me a lot. 

[rubenmarin]

Ok, my bad english and fast reading skipped the 'count' part, maybe with:

=RangeMax(0, RangeMin(1, Sum(Aggr(If(Sum(TOTAL <Name> [total margin])<0, -1, 1),Name))))

[polingolingo]

Was the attachment meant to be the same as what you've sent previously? 

[rubenmarin]

No, it wasn't, I try again.

To show result 0 as green just add 1 to the expression:

=RangeMax(0, RangeMin(1, 1+Sum(Aggr(If(Sum(TOTAL <Name> [total margin])<0, -1, 1),Name))))

[polingolingo]

I guess im getting more confused. sorry about this. 

Shouldnt the Red results = 3 (as 3 deals made <0 total margin). What you've built shows red = 0.

Ideally I have a light that shows Green = 1 & Red = 3. As below, 

 

[rubenmarin]

That 0 or 1 is only to show the lights, to show the number you need another expression.
can you upload a sample of what chart are you using and you want to show this values?