How to highlight in colours which numbers do not belong in a specified range

[Zachblaster]

Hi everyone, hope you are all having a great day.

I am creating my first proper PowerBI report, and I am tasked with highlighting all the numbers in red that don't go into a specified range of numbers. To be more precise:

In the far left column are the various products we sell. In the column that is encircled I have the current discounts. In the 3 columns on the right is the range I would like the current discount to be within. 

I would like the rows to be highlighted where the "W.Disc." number is not within the range from the "StretchedPrice(Disc-%)" and "FloorPrice(Disc-%)". 

If you have any idea as to which measures can be utilized for this purporse or any if you know any other methods, it would be graetly appreciated! Thank you. 

[Anonymous · Accepted solution]

Hi @Zachblaster ,

Please create a measure with below dax formula, and set the conditional format background color for visual:

Measure =
VAR _a =
    SELECTEDVALUE ( 'Table'[W.Disc] )
VAR _b =
    SELECTEDVALUE ( 'Table'[StretchedPrice] )
VAR _c =
    SELECTEDVALUE ( 'Table'[FloorPrice] )
VAR _result =
    IF ( _a >= _b && _a <= _c, "Green", "Red" )
RETURN
    _result

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Zachblaster]

Thank you so much! 
Another questions while I am at it. 

I have been asked to create another column that displays how far the W.Disc% value is from the upper or lower limit (outside the StretchedPrice and FloorPrice). So for example, if the W.Disc% is 56% and the lower limit is 60%, then I would like it to display the value 4%. (or -4%) Once again, you are a huge life savour. Can't express my thanks enough.

Best regards,

Zach

[Zachblaster]

Thank you once again i found the conditional formatting button. 

Unfortunately in conditional formatting you are only able to base the rule on 1 column. I need for the condition to be based on 2 rules ( of: IF below the floor target & above the target = red colour). Is there any measure you could perhaps think of that you allow me to get this information? Thank you

[Zachblaster]

Thank you for your input.

I unfortunately am unable to find the specific field you're pointing to in your screenshot. 

Where on the below screenshot could i find conditional formatting? 

[yakamoz007]

Here must be the name of the columns "W.Disc." etc. Open this. There where you can see the colums which you inserted in you Widget

[yakamoz007]

Hi you can use the conditional formatting.