Conditional Format Based On Single Calculated Measure

[tmwsccsh]

Hi all,

First posting here and I'm hoping someone can give me a pointer on something I am trying to achieve.

I am creating a dashboard to show call statistics which is based on a couple of Excel spreadsheets.  Focusing on the "Calls/Meetings" box on the top-left of the below, this is a measure that I have created and placed in a box on its own by simply dividing the "Meetings Booked" speedo figure into the "Number of Calls" speedo.  The resultant calculated figure is correct.

What I need to be able to do is set it so that if the value in the Calls/Meetings box is less than 2% it displays in Red.  If it is between 2% and 3.2% it displays in Green, and if above 3.2% it displays in Yellow; the idea being we can visually see straight away if we are looking at a bad, good or excellent ratio.

I can see how to apply conditional formatting based on values of data, but I can't see how to apply it to the created measure I have - when I go into the Conditional Formatting tool I can see the measure to select, but the "Ok" button to choose it is greyed out...

How can I apply the colouring that I am looking for to the Measure box so that the display is as required?

Thanks in advance for any guidance.

Regards

TMW

[v-alq-msft · Accepted solution]

Hi, @tmwsccsh 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

You may create measures as below.

Calls/Meeting = [Meeting BOOKED]/[Number Of Calls]
Visual Control = 
IF(
    [Calls/Meeting]<0.02,
    "red",
    IF(
        [Calls/Meeting]>=0.02&&
        [Calls/Meeting]<=0.032,
        "green",
        IF(
            [Calls/Meeting]>0.032,
            "yellow"
        )
    )
)

Then you need to make the card visual selected go to 'Visualizations' ribbon=>'Format'=>'Data label'=>'Color', set as below.

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tmwsccsh]

Hi Allan,

That is brilliant - thank you so much for putting that together.

Really useful.

Regards

Tim

[amitchandak]

@tmwsccsh , If you want to use a measure in conditional formatting. you need to use field options. and select the measure post that.

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

example of measure

Color Date =
var _min =minx(allselected(Date,Date[Year])
return
 Switch( true(),
 FIRSTNONBLANK('Date'[Year],year(TODAY()))-_min =0 ,"lightgreen",
  FIRSTNONBLANK('Date'[Year],year(TODAY()))-_min =0 ,"blue",
 "red")

if(FIRSTNONBLANK(Table[Value],"true")= "true","green","red")

if(FIRSTNONBLANK(date[date],blank())= tofay(),"green","red")
if(max(date[date])= tofay(),"green","red")

Colour = 
SWITCH(TRUE(),
Table[Date] < TODAY(), "red",
Table[Date] = TODAY(), "orange",
"green")

Color sales = if(AVERAGE(Sales[Sales Amount])<170,"green","red")
Color Year = if(FIRSTNONBLANK(Table[Year],2014) <=2016,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow"))

Color  = if(FIRSTNONBLANK(Table[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170 
,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow"))
Color sales = if([Sales Today] -[sales yesterday]>0,"green","red")

[tmwsccsh]

Hi, Thanks for the response.

Have to be honest, it was a bit more complex than I was expecting.

However, I have made an interesting discovery.  Turns out it wasn't the text that required a different colour but the background.

Much easier - I simply created Background colour for the boxes based on the conditions below...

And that has worked fine and does exactly what was needed.

I got a pointer from what you sent over though so thank you very much for the information.

Regards

Tim