Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amconnel
Resolver II
Resolver II

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 II
Resolver II

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 II
Resolver II

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

 

MFelix
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.