6 Replies Latest reply on May 16, 2018 12:30 PM by Crystal Lewis

# Changing quadrant colors when reference line changes

Hi, I have made a quadrant chart where each quadrant is a different color using the calculation below (where 3.10 is the average Reprimand Rate and 3.11 is the average Praise Rate with all the data):

If [Rep Rate]<=3.10 and [Praise Rate]<3.11 then "Gray"

ELSEIF [Rep Rate]<3.10 and [Praise Rate]>=3.11 then "Green"

ELSEIF [Rep Rate]>3.10 and [Praise Rate]>=3.11 then "Orange"

else "Red"

END

However, I actually want to be able to use <= "reference line"  because if I filter on something like School Name, the average x and y changes. And then the quadrant colors are no longer correct.  Is there a way to do this?  Thank you in advance for any help.  I attached a picture of the chart.

• ###### 1. Re: Changing quadrant colors when reference line changes

Hi Crystal

you can use an LOD of determine the to determine the averages involved something like   {include [school name] :average(Reprimand Rate)} and

{include [school name] :average(Praise Rate)}   and place them in place of the fixed values in your formula above

note if you plan to change other dimensions you can include those in the lod statements also

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Changing quadrant colors when reference line changes

Hi Jim,

Thank you so much for your quick response!  So I am trying your method and maybe I'm not doing it correctly because it is giving me a measure rather than a dimension and I'm not sure how to add this to color (If I drag the variable to "color" it shows CNT of the variable and gives "1" .  Is this formula below correct?

If [Rep Rate]<={INCLUDE [School]:AVG([Rep Rate])} and [Praise Rate]<{INCLUDE [School]:AVG([Praise Rate])} then "Gray"

ELSEIF [Rep Rate]<{INCLUDE [School]:AVG([Rep Rate])} and [Praise Rate]>={INCLUDE [School]:AVG([Praise Rate])} then "Green"

ELSEIF [Rep Rate]>{INCLUDE [School]:AVG([Rep Rate])} and [Praise Rate]>={INCLUDE [School]:AVG([Praise Rate])} then "Orange"

else "Red"

END

Also, I should probably mention I have unchecked "aggregate measures".  I'm not sure if that matters for this formula or not.

• ###### 3. Re: Changing quadrant colors when reference line changes

this is why we want to see your twbx workbook when you post a question

all that message means is that something in you formula is aggregated and other things are not

wrap all of the [rep rate} and the [Praise rate]  in Min()

Jim

• ###### 4. Re: Changing quadrant colors when reference line changes

Hi Jim,

I attached the workbook.  I'm still trying to get it to work.  Thank you again!

• ###### 5. Re: Changing quadrant colors when reference line changes

Hi Crystal,

I think you want to use a FIXED formula without aggregating at the level of School. If I specify School in the LOD, then it doesn't seem to come out correctly when all schools are selected, because when all schools are selected we don't want an average at each school level, we want the average of all the detail regardless of school.

If you use FIXED, you can make the school be taken into account by adding the School Filter to Context as shown in the screenshot below. Also, I think you want the Rep Rate in the second clause to be "<=" rather than "<".

I have also created the Fixed Avg Praise and Rep rates as their own calculations, and added them to the Level of Detail in the view so that we can use them for the reference lines.

When in the view, change them to "Attribute".

Now use these calculations as your reference lines:

Workbook is attached. It seems to be working correctly .

Best,

Jennifer

• ###### 6. Re: Changing quadrant colors when reference line changes

This works brilliantly!  Thank you so much for your help!