Colour expression help

[davehutchinson]

Hi there. I'm trying to apply conditional colouring to a pivot table but my script isn't working and I can't for the life of me work out why.

I have a measure in my pivot table of:

=SUM(AssistantUsed)/SUM(AssistantIssueDetected)

Depending on the number I want to colour the box of the pivot table:

>0.9 Green

>0.1 Amber

<0.1 Red

The expression I've created for this is as follows.  however its not working at all, and I really don't understand why.  Can anyone spot an issue with it?

IF(SUM(SAUsed)/SUM(SAIssueDetected)>=0.90,'GoGreen',
IF(SUM(SAUsed)/SUM(SAIssueDetected)>0.11,'b6b700',
IF(SUM(SAUsed)/SUM(SADetected)>=0,'Red')
)
)

[anat]

IF(SUM(SAUsed)/SUM(SAIssueDetected)<=0.1,red(),
IF(SUM(SAUsed)/SUM(SAIssueDetected)>0.1 and SUM(SAUsed)/SUM(SAIssueDetected) <0.9,'b6b700',
IF(SUM(SAUsed)/SUM(SADetected)>=0.9,green())
)
)

[Chanty4u]

Try this

if(SUM(AssistantUsed)/SUM(AssistantIssueDetected) >= 0.9, 'Green',

if(SUM(AssistantUsed)/SUM(AssistantIssueDetected) > 0.1, 'Amber',

'Red'

))

[qv_testing]

Try this

IF(SUM(SAUsed)/SUM(SAIssueDetected)<=0.1,red(),
IF(SUM(SAUsed)/SUM(SAIssueDetected)>0.1 and SUM(SAUsed)/SUM(SAIssueDetected) <0.9,rgb(255,191,0),
IF(SUM(SAUsed)/SUM(SADetected)>=0.9,green())
)
)

[davehutchinson]

Thanks all.  will try all these suggestions as soon as I'm able! Cheers!

[davehutchinson · Accepted solution]

Hi all.  I eventually found the solution but wasn't as straight forward as I'd have hoped.  but this one worked in the end after trying lots of different ways!!!

IF(AVG({<[SmartAssistantUsed]-={0}>}[SmartAssistantUsed]/[SmartAssistantIssueDetected])<.11, Red,
IF(AVG({<[SmartAssistantUsed]-={0}>}[SmartAssistantUsed]/[SmartAssistantIssueDetected])<.90, Amber,
IF(AVG({<[SmartAssistantUsed]-={0}>}[SmartAssistantUsed]/[SmartAssistantIssueDetected])<=1.0,Green)))