How do I create a conditional 'Colour Measure' for the 'value' column?

[HamidBee]

Hi All,

I have created a measure to say that if the 'value' is greater than 0 then the colour of the bar would be green and if it is less than 0 then the the colour of the bar would be red. I seem to get an error when I enter the table name and column name I'm not sure if I'm doing something wrong. I would kindly like to ask what the values 'y' and 'x' would be replaced with?

 

Below was the error:

Thanks in advance,

[Anonymous · Accepted solution]

Hi @HamidBee ,

You are creating measure, you will need to use SELECTEDVALUE() or SUM() function to capture the value from the visual.

For example:

color = SWITCH(TRUE(),SELECTEDVALUE('2021'[value])>0,"#006400",SELECTEDVALUE('2021'[value])<0,"#800020")

Use SELECTEDVALUE() or SUM() or AVERAGE() depend on your needs.

Best Regards,

Jay

[HamidBee]

Thank you for your answer. This is very clean and simple. 

[negi007]

@HamidBee you can create conditional like below

Color_Code = SWITCH(TRUE(),SUM('Table'[sales_qty])<40,"#000000",SUM('Table'[sales_qty])<80,"#e60000","#2d862d")

 

you can modify the hexcode of the color and conditioanl for color as per your choice

[HamidBee]

Thank you for your reply. I wrote a reply to "amitchandak" below explaining in more detail what I was hoping to do. 

I could use what he suggested:

if(sum(2021[Value]) >0 , "Green", "Red")  

but if I create a bar chart to say the measure 'Total B vs Months', the bars would all remain green since the sum of the 2021 values is always greater than zero. So if there is a negative number it would still remain green.

I could use:

SWITCH(TRUE(),[Total A]>0,"Green", [Total A]<0,"Red")

However, this would mean I'd need to create a seperate measure for each category which I can do but I'm trying to figure out a way to avoid this if possible.

Side note: Why is conditional formatting not available when creating a bar chart with two ore more categories in the values section?

[amitchandak]

@HamidBee , You need to use a measure

example

if(sum(2021[Value]) >0 , "Green", "Red")

Other example

Colour =
SWITCH(TRUE(),
max('Table'[Month Year]) = "Jan-2020", "red",
Max('Table'[Month Year]) < "Feb-2020", "orange",
//keep on adding
"green")

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

[HamidBee]

Thank you. The first code you wrote is closer to what I want to do however if I use it it would always make the bar green as the total is always greater than zero. What I was o=planning to do is to make a bar chart for each category A,B,C,D & E where the colour measure would work for each one. So Total A, Total B etc. I understand that it could be done by creating an individual colour colur measure for each one but it would be lengthy and I was wondering whether I could just make one colour measure that would take all scenarios into consideration. 

[Anonymous]

Hi,

Did you findout any solution for this? If so can you please share the measure formula. I also need to create the same color code for different categories

Thanks in Advance....