Forum Discussion
How do I create a conditional 'Colour Measure' for the 'value' column?
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,
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
7 Replies
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
Thank you for your answer. This is very clean and simple.
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
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.
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?
