Forum Discussion
Conditional Formatting - Highlight Lowest Value(s)
Need some help in figuring out how to apply conditional formatting that highlights the lowest value(s) (Rating Score) in each column by location (BMCA, BMCAR, BMCH etc.) for a given Year/Month. See example below.
I've tried applying conditional formatting to Avg. Reception, but it keeps highlighting the lowest value in general, not necesarily the lowest value by Year/Month by Location.
Any help with this would be greatly appreciated!
Hi Anonymous
In your case, you need to create a measure to help set the conditional formatting. I create a sample with some data and use below measure. The measure is to compare the lowest value with each value and return 1 when a value equals to the lowest value. Then format the values by Rules when color measure returns 1.
Color Measure = VAR _table = SUMMARIZE ( FILTER ( ALL ( 'Table' ), YEAR ( 'Table'[Date] ) = MAX ( DimDate[Year] ) && MONTH ( 'Table'[Date] ) = MAX ( DimDate[Month] ) ), 'Table'[Location], "avg", [Avg.Value] ) VAR _minAvg = MINX ( _table, [avg] ) RETURN IF ( [Avg.Value] = _minAvg, 1, 0 )Attach the pbix for your reference.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
7 Replies
Hi Anonymous
In your case, you need to create a measure to help set the conditional formatting. I create a sample with some data and use below measure. The measure is to compare the lowest value with each value and return 1 when a value equals to the lowest value. Then format the values by Rules when color measure returns 1.
Color Measure = VAR _table = SUMMARIZE ( FILTER ( ALL ( 'Table' ), YEAR ( 'Table'[Date] ) = MAX ( DimDate[Year] ) && MONTH ( 'Table'[Date] ) = MAX ( DimDate[Month] ) ), 'Table'[Location], "avg", [Avg.Value] ) VAR _minAvg = MINX ( _table, [avg] ) RETURN IF ( [Avg.Value] = _minAvg, 1, 0 )Attach the pbix for your reference.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.This seems to hit the nail on the head as to what I'm looking to achieve. The only issue I'm running into is when I attempt to format the values by rules when color measure is 1. I get the following error message when attempting to do so:
I triple checked all of the data types to ensure they are all correct and am still recieving this message. I even updated the data types according to what you have in the example provided and still no luck. I'm sure it's something simple, but being that I'm fairly new to creating measures, I'm not entirely sure what I'm missing? Also, here is the DAX formula I'm using based on the one provided.
Hi Anonymous
Can you first check whether the measure returns correct results or not? You can put it into the matrix as a value field to check it. Just like I do in my sample. And what is the [Avg.Value] in your code? In my code, it is a measure which returns a decimal number of the average value. Is yours a measure too?
Jing
Anytime I try and add the measure whether it be as a value field or by conditionally formatting the Avg. Reception by the by the rules where color measure is 1, it gives me the same error message and I'm not longer able to see the matrix (see below).
[Avg. Value] is a measure I created that returns the Avg of [Avg. Reception] (Survey Results). I can still get the average without this measure by just summing [Avg. Reception] by Avg as opposed to Sum. I just wasn't sure if an additional measure needed to be created for the sake of the Color Measure? Particularly the highlighted portion of the formula -
I tried the following and still no luck: