Forum Discussion
Conditional formatting variance percentage
Hi,
I am trying to create conditional formatting on a variance percentage measure that I created in my report. The logic is if the percentage is less than 100% then green font and greater than 100% then red front. My measure is
VAR % = DIVIDE(ACTUALS,BUDGET).
Below is what I have and it is not working.
I tried it several ways like this and it still did not change. Not sure what I am doing wrong.
8 Replies
I tried it several ways like this and it still did not change. Not sure what I am doing wrong.
This works. I was applyting it to values only and I have a matrix so the values are rolling up to the total. The total was not chaning and I did not think to expand to see the values. Thank You
This one works too when I applied it to values and totals. I have a matrix so I was only seeing the totals that is why the color was not changing. Thank you for this solution.
The problem is your range is looking at 1 to max and 0 to -1 (no number is >= 0 and <= negative 1) so you would only be formatting numbers > 100%. You want it like this.
I changed it to Min & Max as you requested but when I select "OK" nothing changed and when I look back at the formatting it changed back to 0 & 100.
That is how it handles the MAX and MIN as 100 Percent and 0 Percent. These are not related to your % calculations, it is the 100% range (max number). The formatting is working in my sample file:
I have attached it for you to look at.
The accepted solution correctly identifies the issue with your range setup. The fix: when your variance measure returns a ratio (0.8 = 80%, 1.2 = 120%), the conditional formatting range needs to use the decimal values, not the percentage display:
- Rule 1: If value is >= 0 AND <= 1 → Green font
- Rule 2: If value is >= 1 AND <= maximum → Red font
Or cleaner, use a color measure:
Variance Color =
IF(
DIVIDE([Actuals], [Budget]) <= 1,
"#375623", -- Green
"#C00000" -- Red
)Apply via Format → Conditional Formatting → Font color → Field value → select Variance Color.
For Finance teams where the goal is to have variance columns with built-in color coding without writing a separate color measure per metric, Flexa Tables on AppSource handles positive/negative variance color automatically no DAX color measure needed
