Conditional formatting based on rules

[New_worker]

Hi -

Here is a confusion on below question. The West is marked in blue when the conditional formatting rules set as below. Why does it not match with the third conditional rule (orange)? But it falls into orange when I reverse the last two conditions..Why does it happen? Is there any unexpected mistake in Power Bi when using Percent and Number together? 

Hope someone can help me on this question. Thank you in advance.

reverse: 

[v-echaithra]

Hi @New_worker ,

We wanted to follow up to see if the issue you reported has been fully resolved. If you still have any concerns or need additional support, please don’t hesitate to let us know, we’re here to help.

We truly appreciate your patience and look forward to assisting you further if needed.

Warm regards,
Chaithra E.

[v-echaithra]

Hi @New_worker ,

We would like to confirm if you've successfully resolved this issue or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.

[v-echaithra]

Hi @New_worker ,

We wanted to follow up to confirm whether your issue has been successfully resolved. If so, we would greatly appreciate it if you could share the solution that worked for you, as it could be beneficial for other community members facing similar challenges.

If the issue is still ongoing or if you need any further assistance, please don't hesitate to let us know. We’re more than happy to continue supporting you.

Thank you for your patience, and we look forward to your response.

Best regards,
Chaithra E.

[New_worker]

Thanks both @danextian @amitchandak . I think I got the point why it shows orange when it is reversed to the last condition. 

[danextian]

Hi @New_worker 

Is _Delta supposed to be percent or number? You've inconsitently used percent and number in your conditional formatting setup.

Nothing will fit between 0.5 and 0 and what is >=0% but less than 0?

[New_worker]

Hi @danextian,

_Delta is number as you see in the bar chart. The reason why I use "if values >=0 percent and < 0 number" is to match negative values. 

I am confused why West (-33K) cannot be matched with the orange condition when it is placed as the third condition but can be matched when it is placed as the last one.

Also, I dont think 50 percent means 0.5..it should be median of the dataset. 

[danextian]

Even though the dialogue box says percent, Power BI actually interprets the input as a decimal. So if you enter values like >= 0 and < 0, nothing will match because those conditions don’t include any valid numbers. To capture negative values, switch the option format to Number instead. Then, delete the lower input value so it defaults to Min which effectively means greater than or equal to the minimum. You can apply the same idea for the upper limit by deleting the value so it defaults to Max.

[New_worker]

Hi @danextian,

Thanks for your reply. I tried, but it will go back to 100% percent as the upper limit, and 0 percent for the lower limit when I re-open the conditional formatting (fx icon). I think, 100% percent and 0 percent are the same as what you mentitioned max (number) and min (number). 

My question is that why West (-33K) cannot be matched with the orange condition when it is placed as the third condition but can be matched when it is placed as the last one.

[danextian · Accepted solution]

The option switching to percent is bug but it uses the option selected when applied. Alternatively, you can write a measure that returns a color based on certain conditions and use it in conditional formatting as field value (select field value instead of rules).

SWITCH (
    TRUE (),
    [my measure] < 0, "color/hex/rgb/rgba",
    [my measure] > 0, "color/hex/rgb/rgba",
    "black"
)
--use certain colors like red, blue, green, gold, gray, etc which Power BI can interpret
--use hex, RGB or RGBA colors

[amitchandak]

@New_worker , Please check some are number some are percent. If you have percent measure. It is also a number usually between 0 to 1 like 25 % is usually .25. 

Percent here means distribution. means I want 1% to 50% of the data as red.

Please correct all percent as number are try out  

[New_worker]

Thanks @amitchandak  for the reply. 

Could you help explain why West (-33K) cannot be matched with the orange condition when it is placed as the third condition but can be matched when it is placed as the last one? I think -33K should be under 50%-100% data range, so it should be marked in orange when it is placed as the third condition, but it is not...