cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amconnel
Resolver I
Resolver I

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.

Composition.png

InkedTable_LI.jpg

1 ACCEPTED SOLUTION
amconnel
Resolver I
Resolver I

amconnel_0-1623687615066.png

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.

amconnel_1-1623687719233.png

 

View solution in original post

10 REPLIES 10
amconnel
Resolver I
Resolver I

amconnel_0-1623687615066.png

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.

amconnel_1-1623687719233.png

 

View solution in original post

MFelix
Super User III
Super User III

Hi @amconnel ,

 

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.

 

MFelix_0-1615572603839.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



This is great help so far! I have added the measure and am struggling to choose this as the 'Based on Field' under the advanced controls of conditional formatting. There does not appear to be an issue with my measure, but I am assuming something has to be wrong. Can you help me see what it is?

amconnel_0-1615844047646.png

 

Hi @amconnel ,

 

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".


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hello @MFelix,

 

Thanks for the tip. I fixed this small error, and this measure is still greyed out when I try to select it as the 'Based on value' in conditional formatting. Any ideas on what to do now?

Hi @amconnel ,

 

I have used the same measure has you and everything is working properly.

 

Can youn share the screen of the image of the measure again maybe something has missed me.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



You can just barely tell in the second photo that Cond_Format is slightly lighter than the other dimensions and measures. When I hover over it to choose it, it does not select like the others will.Screenshot 2021-03-16 090005.pngScreenshot 2021-03-16 090157.png

Hi @amconnel ,

 

the problem is that you are selecting color scale you need to select the Format by Field value:

MFelix_0-1615904047853.png

In this measure you are forcing the result of the measure to be a color code so you need to get the color code that is the measure/field value.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



I suspected that was the case. I have switched it to that, and I am able to select the cond_format measure. However, stilll, nothing happens on my table. The values are still all showing as black.

 

I have tried applying it to all three options (values only, totals only, values and totals).

Hi @amconnel 

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors