Trying to apply Conditional Formatting based upon multiple fields

[Anonymous]

Hello,

I am trying to figure out how to write a Measure which will allow me to use conditional formatting based upon multiple fields within a matrix.  I've seen something similar done which highlighted maximum values in each row, but in this case ideally I'd like to use a color-coded gradient and I can't quite figure it out. I'm sure there's a DAX formula that can manage this.

My raw data is formatted like below; it is giving a Week Number, Location, Measure Type (aka metric) and the Measure Value for the respective Week/Location:

Below is how I have set up my Matrix.  The top example shows what I get when I tried using Conditional Formatting by measure_value -- this does not work because the values are widely different across each different measure_type, resulting in the low values in Order Release are all green vs the high values in Order Transit are all red.  The second example shows what I am trying to achieve, where there is red and green within each measure_type individually.  

Basically I want to tell PowerBI to apply conditional formatting based upon measure_value, but while considering each measure_type seperate from one another when setting the color scale.

I hope what I am asking about is clear, please let me know if I can clarify anything.

Thanks in advance for any help with this.

Thanks,

DG

[amitchandak · Accepted solution]

@Anonymous , You need Create a measure like an example.

You need to add call conditions. Use max for Non summarized columns 

Switch( True() ,

Max(Table[Measure_type]) = "Order Reference" && [Measure]>=.4 , "Red",

Max(Table[Measure_type]) = "Order Reference" && [Measure]<.4 , "Green",

Max(Table[Measure_type]) = "Order Pick Pack" && [Measure]>=.45 , "Red",

Max(Table[Measure_type]) = "Order Pick Pack" && [Measure]<.45 , "Green",

// Add other

,"White" //Default

)

You can change conditions as per need

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

[Anonymous]

Thanks, this solves it.  I used the formula you shared in tandem with the PERCENTILE function to basically achieve the gradient affect I was looking for, just using stepped values instead of a true gradient.  The video you linked was also helpful. Thanks!