Background colour set in expression

[#1]

Hi Guys

I have a pivot table and I wish to set conditional background colours , but I need to set it on multiple selections,

Basically if 

                  Field [Report] = CBIRE AND [Total] < 70 go Green

                  Field [Report] = CBIRE AND [Total] < 110 go Orange

                  Field [Report] = CBIRE AND [Total] > 70 go Red

AND

                  Field [Report] = ROI AND [Total] < 250 go Green

                  Field [Report] = ROI AND [Total] < 500 go Orange

                  Field [Report] = ROI AND [Total] > 500 go Red

I actually have another 4 rules based on different options in Report and Total to add in. But I have tried the first part using

if(Report='CBINT' and Total <=70,RGB (0,255,0),if(Report='CBINT' and Total<=110,RGB (255,255,0),RGB (255,0,0)))

but this is turning them all red regardless of what is filtered or not.

Can anyone please help ?

Thanks

A

[stigchel]

Working with both none or one selected...

[#3]

Haha OK thats it Ive officially given up Its genuinely not happening for me

See Fig 1 In total column I would have wanted Jan ,Feb , Mar and May to be Green as they are less that 920 and only April Red.

Fig 2 shows that when i drill into a Report it works fine !!!

I've literally gone crazy looking at this

Below is the exact condition.

Thanks so much for your time and help Stigchel ,

if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]<=600,RGB (0,127,0),

if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]<=800,RGB (255,128,0),

if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]>800,RGB (255,0,0),

if(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items]<=70,RGB (0,127,0),

if(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items]<=110,RGB (255,128,0),

if(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items] >110,RGB (255,0,0),

if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]<=50,RGB (0,127,0),

if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]<=75,RGB (255,128,0),

if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]>75,RGB (255,0,0),

if(Report='AIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]<=250,RGB (0,127,0),

if(Report='AIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]<=500,RGB (255,128,0),

if(Report='RAIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]>500,RGB (255,0,0),

if(GetSelectedCount(Report)=0 and [3.1.3 Reconciliations - total number of outstanding items]<=920,RGB (0,127,0),

if(GetSelectedCount(Report)=0 and [3.1.3 Reconciliations - total number of outstanding items]<=1406,RGB (255,128,0),RGB (255,0,0)))))))))))))))

[#4]

Hi there

I have resigned myself to the fact that I will not get the conditional colors on the totals ,

So I have reverted back to your original suggestion Stigchel, in the script I put an if statement

if([3.1.3 Reconciliations - total number of outstanding items]<70,'Green',if([3.1.3 Reconciliations - total number of outstanding items]>70 and [3.1.3 Reconciliations - total number of outstanding items]<110,'Orange','Red')) as [RAG Code],

So created a column that determines value based on Totals field, then using this column ( RAG Code) wrote the following condition into my expression

if([RAG Code]='Green' and Report='CM Banking INT'

or [RAG Code]='Green' and Report='PAYMENTS/I&I'

or [RAG Code]='Green' and Report='AIB Bank/AIB(UK)'

or [RAG Code]='Green' and Report='IRE', RGB(0,127,0),

if([RAG Code]='Orange' and Report='CM Banking INT'

or [RAG Code]='Orange' and Report='PAYMENTS/I&I'

or [RAG Code]='Orange' and Report='AIB Bank/AIB(UK)'

or [RAG Code]='Orange' and Report='IRE', RGB (255,128,0),

if([RAG Code]='Red' and Report='CM Banking INT'

or [RAG Code]='Red' and Report='PAYMENTS/I&I'

or [RAG Code]='Red' and Report='AIB Bank/AIB(UK)'

or [RAG Code]='Red' and Report='IRE', RGB (255,0,0),

RGB(255,255,255))))

So the colors will only change when I select a report , failing that they will stay white, its not ideal but its a workaround.

Cant thank you enough for all your help and time

Anne