Forum Discussion
Conditional Formatting for Min/Max Values in Matrix
Hi,
I am needing to conditionally format the values in this table to show with red font color if it is the lowest value within the quarter and green font color if it is the highest value within the quarter. For example in Q4: the value under FY15 (0.83) should be colored red, and the value under FY20 (1.70) should be colored green. I believe an IF statement is what I'm looking for, but I'm having a bit of trouble pinpointing exactly how to format the measure. Within this measure, I need to be able to include only certain values from the table. I do not want Q2-Q4 of FY21 to be included in these records. I need to be able to easily edit that part when rolling the dashboard forward. (i.e. In the future, I need to be able to easily include Q2 FY21 along with everything else included prior.)
Any and all help is appreciated.
This is the measure I ended up using and it worked perfectly as you can see below. (The table was slightly renamed since the original posting of this question.)
Here is the output, just as desired: (Q2 FY21 is now included as time has passed and the financials were finalized.) The numbers blacked out were excluded from the formatting by using the filters in the measure.
10 Replies
Hi Anonymous ,
You need to create the following measure:
Cond_Format = VAR quarterselection = SELECTEDVALUE ( 'Table'[QuarteR] ) VAR temp_table = FILTER ( ALL ( 'Table'[FY], 'Table'[QuarteR], 'Table'[Value] ), 'Table'[QuarteR] = quarterselection ) VAR mimminumvalue = MINX ( temp_table, 'Table'[Value] ) VAR maximumvalue = MAXX ( temp_table, 'Table'[Value] ) RETURN SWITCH ( SUM ( 'Table'[Value] ), mimminumvalue, "Red", maximumvalue, "Green" )Only thing that I'm missing is the filter for the Q2 - Q4 2021 is this based on the fact that the Q2 is not yet finish?
If you tell me the rule we only need to adapt.
Hi Anonymous ,
To use color codes in measures tyou need to have the codes with an hashtag and the six digit code, on the first colour you only have "FF0000" you need to had the # so it would bve "#FF0000".
This is the measure I ended up using and it worked perfectly as you can see below. (The table was slightly renamed since the original posting of this question.)
Here is the output, just as desired: (Q2 FY21 is now included as time has passed and the financials were finalized.) The numbers blacked out were excluded from the formatting by using the filters in the measure.
