How to highlight both column's row if both are at 100 %

[jay_patel]

Hello All,

We know that we can apply conditional formatting on single column based on comparing of 2 columns. But here i want to compare both columns and if both are containing 100% then that row should be highlighted.

Only if they have 100%.

col A	col B
10%	12%
100%	96%
40%	40%
100%	100%

Need to highlight only row 4th's column A & B, as they both have 100%.

Thanks in advance.

[AnalyticsWizard · Accepted solution]

@jay_patel 

To highlight rows where both columns contain 100%, you can use the following approach with conditional formatting in Power BI:

1. Select the table visual where you have your data.

2. Go to the "Conditional formatting" options for column A.

3. Choose "Background color" or "Font color" based on your preference for highlighting.

4. Click on "Advanced controls".

5. In the dialog that appears, set the format style to "Rules".

6. For the rule, choose "Based on field" and select the value of column A to compare.

7. Set the type to "Number".

8. Create a rule where the value is "is" and type in `1` (which represents 100%).

9. Repeat steps 2 to 8 for column B.

10. For both columns, in the rules section, add an additional rule by clicking on "Add rule". Choose "is" and type in `1`.

11. In the "Apply to" dropdown, ensure that you select "All cells" in the column.

Here is a conceptual example of the rules you might set:

For column A:
- If Column A is 1 (100%), and Column B is 1 (100%), then highlight.

For column B:
- If Column B is 1 (100%), and Column A is 1 (100%), then highlight.

This should be done using the "and" condition, ensuring both conditions are true for the formatting to be applied.

Please note that the percentages need to be stored as decimal numbers in Power BI for conditional formatting to work properly. If they are stored as text with a percent sign, you would need to convert them to numbers first.

After setting up these rules, your table should highlight the entire row where both Column A and B have 100% (or a value of 1 if stored as a decimal).

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[v-kongfanf-msft · Accepted solution]

Thanks for the reply from @Jonvoge , please allow me to provide another insight:

Hi @jay_patel ,

If you want to show only the results that satisfy the condition, you can create a formula similar to the following and put the measure into the FILTER.

result_ = 
IF ( MAX ( 'Table'[col A] ) = 1 && MAX ( 'Table'[col B] ) = 1, 1, 0 )

If you want to highlight the rows that satisfy the condition, you can a formula like the following and apply it in conditional formatting.

result2 = 
IF ( MAX ( 'Table'[col A] ) = 1 && MAX ( 'Table'[col B] ) = 1, "red", "white" )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnalyticsWizard · Accepted solution]

@jay_patel 

To highlight rows where both columns contain 100%, you can use the following approach with conditional formatting in Power BI:

1. Select the table visual where you have your data.

2. Go to the "Conditional formatting" options for column A.

3. Choose "Background color" or "Font color" based on your preference for highlighting.

4. Click on "Advanced controls".

5. In the dialog that appears, set the format style to "Rules".

6. For the rule, choose "Based on field" and select the value of column A to compare.

7. Set the type to "Number".

8. Create a rule where the value is "is" and type in `1` (which represents 100%).

9. Repeat steps 2 to 8 for column B.

10. For both columns, in the rules section, add an additional rule by clicking on "Add rule". Choose "is" and type in `1`.

11. In the "Apply to" dropdown, ensure that you select "All cells" in the column.

Here is a conceptual example of the rules you might set:

For column A:
- If Column A is 1 (100%), and Column B is 1 (100%), then highlight.

For column B:
- If Column B is 1 (100%), and Column A is 1 (100%), then highlight.

This should be done using the "and" condition, ensuring both conditions are true for the formatting to be applied.

Please note that the percentages need to be stored as decimal numbers in Power BI for conditional formatting to work properly. If they are stored as text with a percent sign, you would need to convert them to numbers first.

After setting up these rules, your table should highlight the entire row where both Column A and B have 100% (or a value of 1 if stored as a decimal).

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[Jonvoge]

Hi jay_patel

I think you can accomplish this, by creating a new measure, and use that for the conditional formatting on both columns:

M_AreBothMeasures100% = IF(Sum(ColA) = 100% && Sum(ColB) = 100%, True, False)

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data