Flag or highlight row across several columns in table based on value condition

[Anonymous]

Hello,

I have the following table and i want to flag or highlight the entire row if the number of Sold items are 90% or more of Stock items. I used a calculated column for the Difference column in a table visualization. Any help is much appreciated!

Week number	Stock	Sold	Difference
1	121	89	32
2	212	200	12
3	142	105	37
4	846	845	1
5	552	500	52
6	231	31	200
7	514	51	463
8	874	551	323
9	548	84	464
10	152	54	98
11	516	156	360
12	614	614	0

[PaulDBrown · Accepted solution]

Create a measure to use for the conditional formatting along the lines of:

flag = 

VAR _Threshold = DIVIDE(SUM(Table[Sold]), SUM(Table[Stock]))

RETURN

IF(_Threshold >= 0.9, "Red")

Select each field and add the conditional formatting using the option of "Field value" and selecting the [flag] measure

You will need to use the measure for the conditional formatting for each column and in the table visual 

[Anonymous]

@PaulDBrown Thanks for your reply! It works. But as an improvement, could you please advise how to make it work as follows for the "Difference" column, based on the following rules:

1. If the number of Sold items are less than 50% of Stock items, then green icon.

2. If the number of Sold items is at least 50% but less than 90% of Stock items, then yellow icon.

3. If the number of Sold items is 90% or greater than Stock items, then red icon.

[PaulDBrown]

Use this measure with the conditional formatting for icons:

Icon flag =
VAR _Threshold =
    DIVIDE ( SUM ( 'Table'[Sold] ), SUM ( 'Table'[Stock] ) )
RETURN
    SWITCH ( TRUE (), 
_Threshold < 0.5, 1, 
_Threshold < 0.9, 2, 
3 )

Use it for the Difference field