Conditional Formatting in a Matrix

[aashton]

Hello,

My tables are set-up as Regional Goals:  Region, Goal (a decimal number percentage) and Regional Totals:  Date, Month-Year, Region, Vacancy # (a decimal number percentage).  I have a matrix displaying Region and Goal on the Row, Month-Year on the Columns and the Average of the Vacancy # as the values.  I'd like to color the backgrounds of the Vacancy # Values conditionally on whether they are higher or lower than the goal.  I right-click on the Value, open Conditional Formatting for background color and choose Format Style as Field Value.  But in the drop-down for What field should we base this on?  It won't let me pick the Goal field, or the Vacancy # field.  Why won't it let me?  How can I set this up?

[v-jingzhang · Accepted solution]

Hi @aashton 

You can create a measure to compare average values with their goal. When it is equal to or over their goal, it returns 1, otherwise it returns 0. Then choose Format style as Rules and select this color measure to be based on. You can change Apply to option to change the conditional formatting range.  

Color Measure = IF(AVERAGE('Regional Totals'[Overall Vacancy])>=AVERAGE('Regional Goals'[Goal]),1,0)

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

[TomMartens]

Hey @aashton ,

pleaese create a pbix file that contains some sample data but still reflects your data model (tables, relationships, calculated columns, and measures), upload the pbix to onedrive or dropbox and share the link. If you are Excel to create the sample data instead of the manual input method share the xlsx as well.

Describe the expected results based on the sample you provide.

Regards,

Tom

[aashton]

https://www.dropbox.com/s/4jpe81pz4gjiwla/Conditional%20Formatting%20Example.pbix?dl=0

The last column, Monthly Average, I'd like to make green if it is equal or over their goal, or red if below their goal.