Forum Discussion
Conditional Formatting - Highlight Lowest Value(s)
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:
Hi Anonymous
In your case, it doesn't matter whether to use an additional measure to calcualte the average value or not . This will not influence the result. The problem is that the measure doesn't get the correct result in your model. From your measure, your columns are all from the same table. The biggest difference is that I use a Date table in my model and have a relationship between two tables. I put Year and Month columns from Date table into the matrix. And in the filter section of the measure, I compare fact table's date with Date table's date. This may be the cause.
Maybe you can consider adding a Date table into the model. This would be helpful in many calculations.
Do You Need a Date Dimension? - RADACAD
If you don't want to add a Date table, can you provide some dummy data of your table so that I can look into the problem further? At present it's difficult to find out the problem without knowing the structure of your table.
Jing