How to apply conditional formatting in the table in Power BI based on three-color rule comparing .

[Gopiraju404]

Hi everyone,

I'm working on table in the Power BI and need help applying conditional formatting to a table using a 3-color logic.

Here’s what I’m trying to achieve:

I have a table with three columns:

• Actual (Previous Year)

• Target (Current Year)

• Current YTD

I want to apply conditional formatting based on this logic:

🟩 Green – Meet target / goal
🟨 Yellow – Less than goal and greater than previous year
🟥 Red –  Less than goal and less than previous year

This is sample data.

Thanks and regards.

[v-ssriganesh]

Hello @Gopiraju404,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I have reproduced your scenario and successfully applied the conditional formatting to the table in Power BI using the 3-color logic you described:

• Green: Current YTD meets or exceeds Target (Current Year).
• Yellow: Current YTD is less than Target (Current Year) but greater than Actual (Previous Year).
• Red: Current YTD is less than both Target (Current Year) and Actual (Previous Year).

I was able to achieve the expected output as per your requirements. For your reference, I’ve attached the .pbix file below, which includes the table with conditional formatting applied. You can download and review it to see the implementation.

Output:

I noticed that your data contains mixed data types, such as percentages (e.g: 75%), text (e.g: "NA", "Track"), and large numbers with suffixes (e.g: 5.5M, 3.5B). To ensure the conditional formatting works correctly, I recommend cleansing the data in Power Query Editor before applying the logic.

Thank you, @Bibiano_Geraldo & @pankajnamekar25 for sharing your valuable insights.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[Gopiraju404]

Thank you for your response. your logic is applying on values but I also want colors  in  Q1 based on the text values in Target and Actual of achivevement.

This is my exact  sample data.

please help

Thanks and regards.

[pankajnamekar25]

Hello @Gopiraju404 

Try this measure

Color Logic =

VAR Actual = SELECTEDVALUE('Table'[Actual(Previous Year)])

VAR Target = SELECTEDVALUE('Table'[Target(Current Year)])

VAR Current = SELECTEDVALUE('Table'[Current YTD])

-- Try to convert text to numeric

VAR ActualVal = VALUE(Actual)

VAR TargetVal = VALUE(Target)

VAR CurrentVal = VALUE(Current)

RETURN

SWITCH(

    TRUE(),

    ISBLANK(CurrentVal), BLANK(), -- No data

    CurrentVal >= TargetVal, "Green",

    CurrentVal < TargetVal && CurrentVal > ActualVal, "Yellow",

    CurrentVal < TargetVal && CurrentVal <= ActualVal, "Red",

    BLANK()

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[Gopiraju404]

I got error.

Please help

Thank you.

[Bibiano_Geraldo]

Hi @Gopiraju404 , 

The error occurs because the column you're using contains mixed data types such as percentages, text (like "N/A"), and numbers. DAX formulas require consistent data types to perform calculations. In this case, values like "N/A" are text and cannot be converted to numbers, which causes the error.

To fix this issue, you need to make sure that the column contains only numeric values. This means you should either:

• Remove the non-numeric values (like "N/A"), or
• Replace them with a valid numeric value, such as 0 or BLANK() depending on your logic.

This will ensure that DAX can process the data correctly.

[ajaybabuinturi]

Hi @Gopiraju404,

Could you please provide sample data of .pbix file

[Gopiraju404 · Accepted solution]

This is sample data.

Please help

Thank you.

[ajaybabuinturi]

Hi @Gopiraju404,

I want to see how the data is in .pbix file(BI report)