cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

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

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

1 ACCEPTED SOLUTION
Community Champion

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

3 REPLIES 3
Community Champion

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Anonymous
Not applicable

@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.

Community Champion

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors