Conditional Formatting

[JayTG123]

Hi all,

I am trying to put some conditional formatting into my table to visually show how many inspections are still required (looking at CAT A Required column) however for some reason it is not working at all. If you look at Gas Transportation Co Ltd - the marker is red when it should be green as we have done more than the required amount. 

The CAT A Completed column is from a dataset with a filter on to show a certain number of Organisations and the CAT A Required Column is a table I have entered in and linked both up together to form the table. 

Hopefully someone can help please ?!

Many thanks

Jay

[v-jingzhang]

Hi @JayTG123 

I have another question: as you said "If you look at Gas Transportation Co Ltd - the marker is red when it should be green as we have done more than the required amount.", you seem to hope to compare "Cat A Completed" with "Cat A Required" for conditional formatting. While in the rules, you compare Sum of Cat A Required with some fixed percent/numbers. This is somewhat ambiguous. If you want to compare Completed values with Required values, you could create a measure to compare the difference between them and set up the conditional formatting based on this measure. 

Best Regards,
Community Support Team _ Jing

[JayTG123]

Hello @v-jingzhang thank you! Do you know what measure I would need to use please to get the difference? 

[v-jingzhang · Accepted solution]

Hi @JayTG123 

You could try

Difference = SUM(Table1[Cat A Completed]) - SUM(Table2[Cat A Required])

Or 

Diff_Percentage = DIVIDE(SUM(Table1[Cat A Completed]) - SUM(Table2[Cat A Required]), SUM(Table2[Cat A Required]))

The first measure is to calculate the difference between two values and the other is to get the difference percentage based on Cat A Required column. You can always change them per your need. In below image, I take Difference for example. Always remember that if you want to set up the conditional formatting based on results of a measure, choose Number rather than Percent in the rules. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[JayTG123]

Thank you so much!! this has worked a treat!! 😊

Jay

[JayTG123]

Hi @TheoC thank you for your response.

I can change from percent to number but the Cat A required column are varied so this wouldnt work? Each Organisation has different required amount.

[TheoC]

Hi @JayTG123, if they are varied, you should be able to change the Conditional Formatting to numbers nstead of percentages.  Alternatively,  you can use Power Query to create Conditional Columns that use the logic "If Company A, then this, else if Company B, then this, else.....and so on" Ultimately, if you want to use the existing columns as they are and apply Conditional Formatting that is different based on each unique Company, you need to ensure there is something that distinguishes your companies to be able to apply the varied parameters for Conditional Formatting. 

[TheoC]

Hi @JayTG123 

The columns in your table look to be a Whole Number or a Decimal Number versus the Conditional Formatting which is referencing Percentages. 

If you change the Percent to Number in the Conditional Formatting screen, or alternatively keep that as is but change the Data Type of your values to Percent, it should work!

Hope this helps.

[TheoC]

@JayTG123 did my post help resolve the issue?

[JayTG123]

Hello @TheoC thank you for your help! It didnt work but as @v-jingzhang said, I will create a measure and then use the conditional formatting from that measure... I was thinking Power Bi could calculate the percentages from the two columns.