Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TCarrasquillo
Helper II
Helper II

Conditional Formatting Question

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.

 

Power BI Capture.PNG

 

 

 

 

 

 

1 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 

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

View solution in original post

4 REPLIES 4
TCarrasquillo
Helper II
Helper II

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.Smiley Happy 

 

 

v-lid-msft
Community Support
Community Support

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.

 

Conditional-Formatting-Question-1.pngConditional-Formatting-Question-2.png

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"
)

 

Conditional-Formatting-Question-3.png

 

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 

 

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

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?

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 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.