Announcements
cancel
Showing results for
Did you mean:
Contributor III

Colour expression help

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',
)
)

Labels (2)

• Script

1 Solution

Accepted Solutions
Contributor III
Author

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)))

5 Replies
Master

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

MVP

Try this

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

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

'Red'

))

Specialist II

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),
)
)

Contributor III
Author

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

Contributor III
Author

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)))