
2. Re: Conditional Formatting across Dimensions based on Calculated Benchmarks
Zahra Dabzadeh Aug 21, 2018 7:57 PM (in response to Zhouyi Zhang)Hi Zhouyi,
Thanks very much for reaching out!
Below is your screenshot edited to show which cells should be colored:
Also for your reference, the screenshot below from Excel (The first and second question are each color coded separately and the color coding is based on the values of "2015 Rating," "2016 Rating" and "2017 Rating" calculated fields):
Let me know if I can clarify further and thanks again!

3. Re: Conditional Formatting across Dimensions based on Calculated Benchmarks
Zhouyi Zhang Aug 21, 2018 9:09 PM (in response to Zahra Dabzadeh)Hi, Zahra
Thanks for the confirm. please find my updates attached with steps in it.
Below is the end result
Please have a try and let me know if you have stuck somewhere.
ZZ

Survey Data_v10.2.twbx 28.5 KB


4. Re: Conditional Formatting across Dimensions based on Calculated Benchmarks
Zahra Dabzadeh Aug 22, 2018 9:26 PM (in response to Zhouyi Zhang)Zhouyi,
Thanks so much for your workbook and the detailed steps
I follow all of the steps and have one question: Did you manually change the Advanced settings in "Edit Colors" for the [2017 Rating] measure?
Another (less important) question if you have an extra minute: In step 2, how do I repeat similar steps without dragging the other measures?
I have no words to describe how much I appreciate your time and help with this... Thank you!

5. Re: Conditional Formatting across Dimensions based on Calculated Benchmarks
Zhouyi Zhang Aug 22, 2018 9:36 PM (in response to Zahra Dabzadeh)Hi, Zahra
Yes, change the color into 3 steps
and to your 2nd question, I meant to repeat step1 and step2 for 2016 rating, 2017 rating as well as diffs,
so finally you will have 5 pairs of step 1 and step2 for your 5 measures individually.
If this helps you solve the problem, please mark my answer as correct, thanks.
ZZ

6. Re: Conditional Formatting across Dimensions based on Calculated Benchmarks
Zahra Dabzadeh Aug 22, 2018 9:48 PM (in response to Zhouyi Zhang)Thanks very much for the quick response, Zhouyi!
Your answer to my second question makes it crystal clear!
For my first question, beyond adjusting the color to 3 steps, did you also manually adjust the Start, End and Center values for the [2017 Rating]?
If yes, I am not sure how I can scale your answer to 100+ questions without having to individually review values for each question and making manual adjustments... Please let me know if this is not the case and I have misunderstood your answer!

7. Re: Conditional Formatting across Dimensions based on Calculated Benchmarks
Zhouyi Zhang Aug 22, 2018 10:42 PM (in response to Zahra Dabzadeh)Hi, Zahra
this is a very good point, I think the reason why I am doing this is I am not sure the logic behind from your case, which/what range of your rating should be colored as green/yellow/red.
So if you have such logic, then we can create a calculation field like
if sum([2015 rating]) < 70 then
'Red'
elseif sum([2015 rating]) >=70 and sum([2015 rating]) < 90 then
'Yellow'
else
'Green'
end
and use this as color rather than use measure.
ZZ

8. Re: Conditional Formatting across Dimensions based on Calculated Benchmarks
Zahra Dabzadeh Aug 23, 2018 8:43 AM (in response to Zhouyi Zhang)Zhouyi,
Thanks very much for pointing me in the right direction
I am having some difficulty creating a calculated field like you mentioned because my logic is more complex and depends on the value of the [2017 Rating] for each row (which changes by row)... In other words, I cannot select two numbers like 70 and 90 and apply coloring to all rows based on these numbers.
This is the logic that I would like to include in my calculated field (and then add this calculated field to Color in the Marks card):
if NOT ISNULL([2015 Rating])
then ([2015 Rating]/ [2017 Rating])
elsif NOT ISNULL([2016 Rating])
then ([2016 Rating]/ [2017 Rating])
elsif NOT ISNULL([2017 Rating])
then ([2017 Rating]/ [2017 Rating])
end
I know the first part of my IF statements (like NOT ISNULL([2015 Rating])) is accurate, but the trouble seems to be in the second part (like ([2015 Rating]/ [2017 Rating]))...
The screenshot below from "View Data" sums up the trouble: In the row underlined in red, the condition NOT ISNULL([2015 Rating]) is met, and Tableau moves on to calculate the expression ([2015 Rating]/ [2017 Rating]), but there is only a [2015 Rating] value for that row and no [2017 Rating] value, so the result I get is NULL...
At least this is what I think is happening...
If you could help me with resolving this issue or coming up with a new calculated field that is based on the changing value of the [2017 Rating] for every row, I would be so appreciative...