I’m using conditional formatting on my pivot table where I’m adding different background colors based on measure values. For example, if the measure is between 1.00 to 1.99, color should be light green. Running into a problem here where values 1.00 and 1.99 aren’t picking up the background color. They remain white. Are min and max values not included in formatting for between conditions?
Did you make sure they are greater than or equal to and less than or equal to?
Yes, even if I use ‘greater than or equal to’ condition, for eg: >=2, 2 doesn’t pick up the color but all numbers greater than 2 do.
Can you show me what you’re seeing?
Also make sure that there is no rounding. For example 1.999999999 would not be blue.
Value 1 in the screenshot should be light green. Instead, its white because its not picking up the background color. My condition is if values are between 1 and 1.99, then use green as the background color. 1 and 1.99 particularly are still white. Looks like between doesn’t include the min and max values in the condition.
Hi @Mansi_Parekh, thanks for sharing your example. Similarly to what Max had already mentioned, I’d expect your described behavior coming from the formatting configured to your values. It looks like your numbers are formatted to use 2 decimal places. As QuickSight is rounding the numbers in that case to two digits, every number >=0.995 and <1.005 will be displayed as 1.00. However, if the underlying number is <1, your formatting condition is not met and, hence, the background color not changed.
How to handle those cases strongly depends on your use case. If you want the conditional formatting to be strictly aligned to the formatted numbers shown in the visual (vs. the actual underlying number), you could simply adjust your range in the condition according to the rounding logic described above.
Did this answer your question? If so, please help the community out by marking this answer as “Solution!”. Thanks!