-
1. Re: Changing quadrant colors when reference line changes
Jim DehnerMay 16, 2018 7:45 AM (in response to Crystal Lewis)
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
Crystal Lewis May 16, 2018 8:23 AM (in response to Jim Dehner)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
Jim DehnerMay 16, 2018 9:15 AM (in response to Crystal Lewis)
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
Crystal Lewis May 16, 2018 9:35 AM (in response to Jim Dehner)Hi Jim,
I attached the workbook. I'm still trying to get it to work. Thank you again!
-
Rates.twbx 20.2 KB
-
-
5. Re: Changing quadrant colors when reference line changes
Jennifer VonHagel May 16, 2018 12:15 PM (in response to Crystal Lewis)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
-
Rates.twbx 24.7 KB
-
-
6. Re: Changing quadrant colors when reference line changes
Crystal Lewis May 16, 2018 12:30 PM (in response to Jennifer VonHagel)This works brilliantly! Thank you so much for your help!