Calculated colors on bubble chart

[#1]

I have created a bubble chart which shows a bubble for each patient in a population.  The x-axis shows the age of the patient and the y-axis shows their average costs.  I've added guide lines to the graphic to split the graph into quadrants.  I want to color the bubbles according to which quadrant they are in.

I've tried to just use simple calculated colors, like:

if(PMPM >= 2500, red(), green()), and

if(column(2) >= 2500, red(), green())

both of these calculations changed the color of all the bubbles to the else condition, in the above cases green().

What I really want is:

      if(Age >= avg(All Age) and PMPM >= 2500, red(),

elseif(Age >= avg(All Age) and PMPM < 2500, green(),

elseif(Age < avg(All Age) and PMPM < 2500, blue(),

elseif(Age < avg(All Age) and PMPM >= 2500, yellow())

THANKS!

[sebastiandperei]

Hi Boyer.

In first place, you could make a Variable with avg (Total Age), this will improve the calculation time, because it calculates only one time, not for every bubble.

In this case, I will name the variable as vAgeAvg (its contents is =Avg(Total Age).

In Color expression, you could try:

If(Age>=vAgeAvg,

     If(PMPM>=2500, red(), green()),

     if(PMPM>=2500, blue(), Yellow())

)

If it isnt the answer you need, please, send a reduced copy for better understanding.

[#3]

I added your code, but now all the dots are being colored yellow because it's the else option in the if statements.

I can't add the extra expression to calculate the avg(Total Age) because it doesn't give me the option to make it invisible.

[sebastiandperei · Accepted solution]

Where are you adding the code?

Please, in the Expressions Tab, at the first one, click on + symbol, and in Background Color, write the expression. I've tryed in your sample and it works.

What you want to make invisible?