Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThis 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?
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYou 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.
Hi @amconnel ,
the problem is that you are selecting color scale you need to select the Format by Field value:
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |