Conditional Formatting Question

[TCarrasquillo]

Hi everyone,

I'm hoping  for a little help with a request I've been given. It seems like this should be a more common request for Conditional formatting but I can't seem to find a solution.

I have a table with some KPI for each of our Area Managers and what I've been asked to do is, conditionally format the table so each manager can see where they fall in relation to the company as a whole.

So, what I need based on the picture below is the first Manager has a labor% of 84, higher then the Company % so it Should be red.

the OT% is lower so should be green, and the labor/HR is higer so red again.

The Company wide numbers are YTD so they change whnever the report is refreshed. Is it possible to write a DAX measure that would do this formatting?

Thanks so much for any help.

[TCarrasquillo]

Thanks  for your help @v-lid-msft ,  it didn't quite provide the answer, but it pointed me in the right direction. It also made me realize I need to get a "sanitized" version of my data sources  so that I can share my model  when I run into these questions. 

[v-lid-msft]

Hi @TCarrasquillo ,

We can set the font color conditional formatting based on a measure as below.

LaborColor = 
SWITCH (
    TRUE (),
    SUM ( 'Table'[Labor] ) >= [CompanyLabor], "#FF0000",
    SUM ( 'Table'[Labor] ) < [CompanyLabor], "#00FF00"
)

The company labor measure can change to another dynamic value.

HrColor = 
SWITCH (
    TRUE (),
    SUM ( 'Table'[Labor] ) >= [CompanyLabor], "#FF0000",
    SUM ( 'Table'[Labor] ) < [CompanyLabor], "#00FF00"
)

Then you can create more color measure for another two values.

OTColor = 
SWITCH (
    TRUE (),
    SUM ( 'Table'[OT] ) >= [CompanyOT], "#FF0000",
    SUM ( 'Table'[OT] ) < [CompanyOT], "#00FF00"
)

BTW, pbix as attached.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more 

[TCarrasquillo]

Thanks for the response, it's at least one step closer to what I need.

The problem I see is that the color formula would require me to manually input the Company "target" values to get the formatting to work.

Which really isn't viable since the numbers change each week when we update our financials.

Anyone else have ideas?

[v-lid-msft · Accepted solution]

Hi @TCarrasquillo ,

The company target values in the color formula can be modified to any dynamic measure like following

CompanyLabor =
AVERAGEX ( ALL ( 'Table' ), [Labor] )

Or you can change this refer to an actual value in a table which would be updated weekly. The color formula will work as you need.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more