Forum Discussion
Conditional formatting 2 columns to return colour
I have few tables as my data source. I created a table to show various columns from different data sources using table visual.
I want to compare two columns based on certain conditions and in return highlight the cells.
Here is my sample data :
Table 1 :
| Material | Material Name | Sales Order |
| MA01 | Alpha | 5123 |
| MA02 | Bravo | 5124 |
| 1245 | Charlie | 5125 |
| MA03 | Delta | 5126 |
Table 2:
| Material | Storage location | Stock |
| MA01 | DE | 5 |
| MA02 | DE | 10 |
| MA03 | US | 6 |
| 1245 | NL | 12 |
Table 3 :
| Material | Delivery quantity |
| MA01 | 2 |
| MA02 | 3 |
| MA03 | 1 |
| 1245 | 10 |
Here is the visual I created based on above data :
New_stock column is created on a column with rule to return values only if the storage location is - DE, NL otherwise return blank.
| Material | Name | Sales order | New_Stock | Delivery quantity |
| MA01 | Alpha | 5123 | 5 | 2 |
| MA02 | Bravo | 5124 | 10 | 3 |
| MA03 | Delta | 5126 | 1 | |
| 1245 | Charlie | 5125 | 12 | 10 |
I want to highlight the rows with below condtion
If Delivery < 90% of stock return "Yellow" and if Delivery >= 90% of stock then no change in the colour.
Could anyone please support and help?
I have tried IFERROR and LOOKUPVALUE to get the columns in one specific table and then do the conditional colour formatting but does not seem to work.
Any help would be appreciated. Thanks.
2 Replies
bansikpopat , you have to create color measure based on these measures you have for Delivery
example
Color =
var _del = divide(sum(Table[Delivery quantity]), sum(Table[New_Stock] ) )
return
if(_del <.9, "Yellow", "white")
You can use the same in conditional formatting using the field value option
How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/1682539
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pane-66e0afcb15f3It does not woek when I try to create a new measure.
My requirement is to compare the delivery quantity and stock quantity. Both of these values are from different tables which are connected using many to many relation.
Example :
1. I am creating a measure in "Delivery" table to reference 90% stock colum from "Stock" table, but it does not suggest me any column from delivery table.
The idea is to basically get the 90% stock and delivery quantity columns in one table to do easier conditional formatting. However, creating new measure to refer columns is not working.
