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

[colinh]

Using exactly your formula I get green, yellow, red.

It might be obvious, but in your first set of data you have CBIRE and in your calculation you have CBINT.  Is it not that?

[#3]

Would you believe the samples that I had loaded had only RED range values loaded ( I know its often the simpliest issues that cause the greatest hassle !!!)

I've just started writing it with all 6 conditions , so please feel free to watch this space cause I reckon I will have more questions when Im joining all the ifs together

Thanks

[colinh]

Just make sure you have as many closing brackets as opening brackets and you'll be fine.

[#5]

Hey Colin

I have used the following and it works fine for all the drill downs ( So when I select a report).

But i need a condition where no reports are selected ( So basically the sum of each of the reports) the formula in bold in that portion but it does not seem to work , can you see if I have made a mistake ?

Or is there a way of saying if field is not selected ?

Thanks

Anne

if(Report='CBINT' and [Total]<=600,RGB (0,255,0),if(Report='CBINT' and [Total]<=800,RGB (255,255,0),if(Report='PAYMENTS/I&I' and [Total]<=70,RGB (0,255,0),if(Report='PAYMENTS/I&I' and [Total]<=110,RGB (255,255,0),if(Report='IRE' and [Total]<=50,RGB (0,255,0),if(Report='IRE' and [Total]<=75,RGB (255,255,0),if(Report='ROI Bank' and [Total]<=250,RGB (0,255,0),if(Report='ROI Bank' and [Total]<=500,RGB (255,255,0),if([Total]<=920,RGB (0,255,0),if([Total]<=1406,RGB (255,255,0),RGB (185,0,0))))))

[#6]

Hi Colin

I also took the opposite and tried when all Reports were selected see below in bold and again got same result all fields were red ,

if(Report='CBINT' and [Total]<=600,RGB (0,255,0),if(Report='CBINT' and [Total]<=800,RGB (255,255,0),if(Report='PAYMENTS/I&I' and [Total]<=70,RGB (0,255,0),if(Report='PAYMENTS/I&I' and [Total]<=110,RGB (255,255,0),if(Report='IRE' and [Total]<=50,RGB (0,255,0),if(Report='IRE' and [Total]<=75,RGB (255,255,0),if(Report='ROI Bank' and [Total]<=250,RGB (0,255,0),if(Report='ROI Bank' and [Total]<=500,RGB (255,255,0),if(Report='ROI Bank' and Report='CBINT' and Report='IRE'and Report='PAYMENTS/I&I' and [Total]<=920,RGB (0,255,0),if(Report='ROI Bank' and Report='CBINT' and Report='IRE'and Report='PAYMENTS/I&I' and [Total]<=1406,RGB (255,255,0),RGB (185,0,0)))))))))))

[colinh]

What are you trying to highlight in red, a total on the chart, or each report?  If it is a total, try this:

=if(Report='CBINT' and [Total]<=600,RGB (0,255,0),

if(Report='CBINT' and [Total]<=800,RGB (255,255,0),

if(Report='PAYMENTS/I&I' and [Total]<=70,RGB (0,255,0),

if(Report='PAYMENTS/I&I' and [Total]<=110,RGB (255,255,0),

if(Report='IRE' and [Total]<=50,RGB (0,255,0),

if(Report='IRE' and [Total]<=75,RGB (255,255,0),

if(Report='ROI Bank' and [Total]<=250,RGB (0,255,0),

if(Report='ROI Bank' and [Total]<=500,RGB (255,255,0),

if(sum([Total])<=920,RGB (0,255,0),

if(sum([Total])<=1406,RGB (255,255,0),RGB (185,0,0)))))))))))

[#8]

Hi Colin

I tried the above, what I wish to happen is , this formula  is on a Pivot Chart,on a list box if I select CBINT and the Total is 700 I wish for the field to go Orange or if I select IRE and its 700 it will go red . ( Using my original statement this was happening)

( using for example there is only 2 reports) If I have not selected anything in the List box for report ( the pivot chart shows the totals) it uses the condition I would expect it to go Orange as the total is 1400 which under the last condition of the formula is < 1406 .

Whats happening with the above statement is when no fields are selected it is remaining green , and on the drill down only the ROI is actually working now

Thanks so much for your time on this

[colinh]

No problem.  I'm not sure I really understand your data - what is the Dimension on your pivot table?  Is it Report, or some other dimension?  Can you attach an example with your data structure and the chart.

I think the problem is that you are trying use the same expression to change the colour for each row and for the total.  What might work better is to include the option for red in each condition, e.g.

if(Report='IRE' and [Total]<=50,RGB (0,255,0),

if(Report='IRE' and [Total]<=75,RGB (255,255,0),

if(Report='IRE' and [Total]>75,RGB (185,0,0),

Then the last condition will only apply to the total.

[stigchel]

You can check the nr of selections (or none) like this:

if(GetSelectedCount(Field)=0,...,...)