Struggle in the background color expression

[Antoine]

I have a pivot table, with multiple fields in it. I want to put the field in green if we reach the goal (or are under the limit), and in red if we don't.

I figured the expression would be : IF( expression of the value > expression of the goal, 'green', 'red')

In many cases, it works just fine: 

If(
Round(Sum({<[Année KPI.autoCalendar.Year]={"$(=Year(Today()-1))"}, [Nom KPI]={"AT & LTA"}>}[Objectif KPI]),1) //value
>=
Count({<[Nb AT et jours perdus$.UPs]=$(=VUP)>}[Nb AT et jours perdus$.Nom]) //limit
,'green'
,'red')

(here the limit value is 0, it's also written next to 'Objectif : ' and concatenated with it)

But sometime it doesnt like for this expression (in this case it's a limit, but doesn't matter):

If(
(1000000*(Sum({<[BDDScrap.UP]=$(=VUP)>} ScrapQuantity)/(Sum({<[BDDScrap.UP]=$(=VUP)>} ScrapQuantity)+Sum({<[BDDOEE.UP]=$(=VUP)>} quantity)))) // value
<
round(Sum({<[Année KPI.autoCalendar.Year]={"$(=Year(Today()-1))"}, [Nom KPI]=$(=VUP), [Type]={"NRFT"}>}[Objectif KPI]),1) // Limit
,'green'
,'red')

The value of the limit returned is 8480 (here it's concatenated with a string in the name expression)

But when the value in the table is higher (9 647; 11 329; 9 351), the color is in green, and it's still green when it's under (8208). I tried to *1 the limit value, I tried to use Num#(limit expression, '0,0').

I also tried changing the expression of the goal/limit by writting directly 8480 in the expression, and it works fine...

So I would be glad if you could help me on that one, since I have no idea what's going wrong...

Kind Regards,

Antoine

[timpoismans · Accepted solution]

Insert your limit measure as a measure into your table and look at its value. The only reason why it wouldn't work, is because your limit value isn't a static 8480.

[Antoine]

Thanks for the tips!

The sum on my limit was messing up, and was multiplying my limit by every line registered for each measure !

Solved it by simply adding a 'distinct' (could also have made an average...)

Have a good day,

Antoine