Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
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 numberStockSoldDifference
11218932
221220012
314210537
48468451
555250052
623131200
751451463
8874551323
954884464
101525498
11516156360
126146140

 

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
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 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

3 REPLIES 3
PaulDBrown
Community Champion
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 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.

 

MakeItReal_0-1659633337238.png

 

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

icon flag.png

 

icons.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors