Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |