Color expression for sum of 2 variables

[Kakay_Ranas]

Hi! I am having a hard time with the color expression. 'CONTRACT C' and 'CONTRACT D' are technically under the same contract so I want their numerators and denominators summed up to get a color expression. Can someone help please?

IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .95 AND MEASURE = 'CS_SERVICE_LEVEL' AND CONTRACT ='CONTRACT A', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND MEASURE = 'CS_SERVICE_LEVEL' AND CONTRACT ='CONTRACT B', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND MEASURE = 'CS_SERVICE_LEVEL' AND CONTRACT ='CONTRACT C', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND MEASURE = 'CS_SERVICE_LEVEL' AND CONTRACT ='CONTRACT D', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND MEASURE = 'CS_SERVICE_LEVEL' AND CONTRACT ='CONTRACT E', Green(270)
, Red(255))))))

[Yoshidaqlik · Accepted solution]

Hi,

I think in the form of Virtual Table should work

Aggr(
IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .95 AND Only(MEASURE) = 'CS_SERVICE_LEVEL' AND ONLY(CONTRACT) ='CONTRACT A', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND Only(MEASURE) = 'CS_SERVICE_LEVEL' AND ONLY(CONTRACT) ='CONTRACT B', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND Only(MEASURE) = 'CS_SERVICE_LEVEL' AND ONLY(CONTRACT) ='CONTRACT C', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND Only(MEASURE) = 'CS_SERVICE_LEVEL' AND ONLY(CONTRACT) ='CONTRACT D', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND Only(MEASURE) = 'CS_SERVICE_LEVEL' AND ONLY(CONTRACT) ='CONTRACT E', Green(270)
, Red(255))))))
,MEASURE,CONTRACT)

 Regards

[Kakay_Ranas]

That worked! Thank you so much!

[Kakay_Ranas]

So the above solution worked when both contracts being summed up have same color results i.e when they both didn't meet targets or both met targets. This month has varying result and that presented a problem. I tried exploring other formula like the one below but no luck, would appreciate other suggestions.

IF((sum({MEASURE = {'CS_SERVICE_LEVEL'},CONTRACT ={'CAREFIRST_A','CAREFIRST_B'}>} NUMERATOR)/(sum({MEASURE = {'CS_SERVICE_LEVEL'},CONTRACT ={'CAREFIRST_A','CAREFIRST_B'}>}DENOMINATOR))>= .90, Green(270)
, Red(255)