Conditional formatting not working

[mogugu_84]

The goal is to create a yellow/red coloring on the "3M G%" column in a matrix table comparing to the total at the bottom, i.e. if it's higher than the total (13.4%), it's yellow. 

i created the example using a rule like this, which is not ideal because the total number "13.4%" could change as the data refreshes:

To improve this, i created a few measures by step and also to help to check if it works properly, see on the card visual:

the measure "3M G% Fyc" equals the value per row (accounts), it does change when i click on each row, and the second measure with "National" equals 13.4% of all acounts, and tested okay. (formula for that is: 

3M G% Fyc National = calculate([3M G% Fyc],all('Top account'[Top Accounts]))

And lastly, i have a color check measure to compare which one is bigger, also tested working properly, when it's 10.7% in this row, the color check is "0", otherwise it would be "1" if the row value is higher than 13.4%

Color check = if([3M G% Fyc]>[3M G% Fyc National],1,0)

However, when i put this rule into conditionally formating, nothing happens, anyone knows why? 

[danextian · Accepted solution]

Hi @mogugu_84 

I'm thinking it's because you used text in your condition instead of a number which is how Rules are being evaluated. "1" is not 1.  Also, please try to use this measure. Select Field Value from the conditional formatting options and use this measure.

3M G% Conditional Formatting =
VAR _total = 
    CALCULATE ( [3M G%], ALLSELECTED () ) 
    -- Calculate the total 3M G% while considering all selected values in the filter context.
    -- This removes any specific row-level filters but still respects slicer selections.

RETURN
    IF ( [3M G%] > _total, "#f6b53d", "#e85e76" ) 
    -- Compare the current 3M G% value against the total.
    -- If the current value is greater than the total, return the color code "#f6b53d" 
    -- Otherwise, return "#e85e76" 

[grazitti_sapna]

Hi @mogugu_84,

When conditional formatting doesn’t change as expected, it’s often due to how the formatting rule is set up versus how the measure is being used. You can modify your DAX as

Color Check Hex =
IF(
[3M G% Fyc] > [3M G% Fyc National],
"#FFFF00", // Yellow
"#FF0000" // Red
)

Now, In your matrix, go to the column settings for “3M G%” and set the conditional formatting to “Format by field value.” Then, select your new color measure (e.g., Color Check Hex) as the field to base formatting on.

Ensure Proper Context:

Make sure that the measures ([3M G% Fyc] and [3M G% Fyc National]) are calculating correctly within the visual’s row context. For instance, verify that [3M G% Fyc National] (which uses ALL) is indeed returning the total value you expect across all rows.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!