Attached is the solution workbook and below is the screenshot.
I created 3 calculations: Rate 1, Goal Rate 1 and Color and used them in the worksheet. Let me know if you have any questions!
Please note that the Blue corresponding to Null is due to the fact that you are not handling some cases in your of conditions.
For Ex- CA State Rate for Measure 1 is 99.5% but as per your IF condition Less Than 5% of 99.8% (Goal Rate) is 94.8%, so, (Rate < Goal Rate*.95) amounts to False. I think you need to re-check the logic for Red and Yellow as both are pointing to less than 5%.
Red -> (Rate < Goal Rate*0.95) and Yellow -> (Rate < Goal Rate*0.9499999)
Demo_Calc_v10.2_v10.2.twbx 11.6 MB
Thank you so much! I have been playing with that workbook and formulas that i must have deleted the second half of the yellow. Yellow is was supposed to be [Rate]>([Goal Rate]*0.9499999) AND [Rate]< [Goal Rate] then 'Yellow'. If you hadn't heard my shout of glee. Let's just say I disturbed the neighbor of my cube. I have not used the FIXED and Max like you have. Would you share the source? I am always seeking to learn. Again, Thank you so very much.
Glad that it worked! I noticed one more thing in your calculation.
In your IF ELSE structure,
Rate >= Goal Rate condition is appearing above Rate = 1 condition
The cursor would never go to the last one.
For Ex - If Rate is 1 (ie 100%), Rate>= Goal Rate will always get satisfied and the cursor would never get to Rate = 1
Let me explain how I approached the problem.
The first thing i noticed in the worksheet was that the Goal Rate was only coming up for City = 'Goal', for others its Null.
Hence, the cursor never satisfies to any IF Condition containing Goal Rate for cities other than 'Goal' because it compares against 'Null'.
Screenshot below of your worksheet, i just added Goal rate to Text to see what value its generating
What we really want is to have the Goal Rate for every data point but the calculation should only consider City='Goal'. This is the reason I used Fixed LOD calc. see similar screenshot below of my worksheet
I created LOD calc for Rate to bring it on the same level of aggregation so that it would allow to compare both fields in your IF ELSE condition.
And, then tweaked the IF conditions to change the order of Rate = 1 condition, so that its not overridden by Rate >= Goal Rate condition.
Let me know if you have any further questions.