Forum Discussion
Question regarding conditional formatting in Line clustered coloumn chart.
I have 2 different tables,
Table 1, with weight of the scrap in each month ,
Table 2, with total tonnage and target of each month.
For example, if my scrap total weight on Jan is X and tonnage of Jan is Y, then X/Y= Z%,
i would like to highlight the Z% value in Red if it is greater than Monthly Target else Green.
Table1
| Weight of Scrap in Kg | Week Num | Ref Month |
| 108.2 | 1 | 1 |
| 92 | 1 | 1 |
| 16.1 | 1 | 1 |
| 36.3 | 1 | 1 |
| 89.6 | 1 | 1 |
| 110 | 1 | 1 |
| 42.2 | 1 | 1 |
| 49.2 | 1 | 1 |
| 421.4 | 1 | 1 |
| 6.2 | 1 | 1 |
| 8 | 1 | 1 |
| 90.4 | 1 | 1 |
| 43 | 2 | 1 |
| 100.6 | 2 | 1 |
| 322.4 | 2 | 1 |
| 110 | 2 | 1 |
| 63.9 | 2 | 1 |
| 466.9 | 3 | 1 |
| 22.3 | 3 | 1 |
| 27.6 | 3 | 1 |
| 422 | 3 | 1 |
| 13.6 | 3 | 1 |
| 26.4 | 3 | 1 |
| 2.6 | 3 | 1 |
| 7 | 3 | 1 |
| 4.8 | 3 | 1 |
| 6.6 | 3 | 1 |
| 92.6 | 3 | 1 |
| 33.2 | 3 | 1 |
| 28 | 3 | 1 |
| 38.8 | 3 | 1 |
| 44.4 | 3 | 1 |
| 83.1 | 3 | 1 |
| 14.2 | 3 | 1 |
| 21.7 | 3 | 1 |
| 12 | 3 | 1 |
| 12 | 3 | 1 |
| 10.6 | 3 | 1 |
| 113.2 | 3 | 1 |
| 18.2 | 3 | 1 |
| 68.2 | 3 | 1 |
| 210 | 3 | 1 |
| 111 | 3 | 1 |
| 262.2 | 4 | 1 |
| 10 | 4 | 1 |
| 200 | 4 | 1 |
| 112.8 | 4 | 1 |
| 28.2 | 4 | 1 |
| 55 | 4 | 1 |
| 97.8 | 4 | 1 |
| 108 | 4 | 1 |
| 208 | 4 | 1 |
| 85.2 | 4 | 1 |
| 40 | 4 | 1 |
| 24 | 4 | 1 |
| 14 | 4 | 1 |
| 11.8 | 4 | 1 |
| 2.2 | 4 | 1 |
| 103 | 4 | 1 |
Table 2
| Month | Tonnage in Kg | YEAR | Target |
| 1 | 1373535 | 2021 | 0.32% |
| 2 | 1727260 | 2021 | 0.35% |
| 3 | 2078505 | 2021 | 0.34% |
| 4 | 2205560 | 2021 | 0.31% |
| 5 | 1436035 | 2021 | 0.36% |
| 6 | 1637310 | 2021 | 0.34% |
| 7 | 1768720 | 2021 | 0.33% |
| 8 | 1912140 | 2021 | 0.33% |
| 9 | 1912000 | 2021 | 0.35% |
| 10 | 2021 | 0.34% | |
| 11 | 2021 | 0.39% | |
| 12 | 2021 | 0.33% |
Hi, Anonymous
According to your description, I think you need to establish the relationship between months first, then you can put the two table fields in one table visual, and create a measure to mark red by using conditional format.
Like this:
Measure = VAR a = SUMX ( FILTER ( ALL ( Table1 ), [Ref Month] = SELECTEDVALUE ( Table2[Month] ) ), [Weight of Scrap in Kg ] ) RETURN IF ( DIVIDE ( a, SELECTEDVALUE ( Table2[Tonnage in Kg] ) ) > SELECTEDVALUE ( Table2[Target] ), "Red" )Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,Community Support Team _ Janey
2 Replies
Anonymous , I doubt clustered line visual if you are using only one measure on bar then you can use conditional formatting on bar. Not on line.
You can a measure based conditional formatting
example
Color sales = if(AVERAGE(Sales[Sales Amount])<170,"green","red")Color Year = if(FIRSTNONBLANK('Table'[Year],2014) <=2016,"lightgreen",if(FIRSTNONBLANK('Table'[Year],2014)>2018,"red","yellow"))
Color = if(FIRSTNONBLANK('Table'[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170
,"lightgreen",if(FIRSTNONBLANK('Table'[Year],2014)>2018,"red","yellow"))Color sales = if([Sales Today] -[sales yesterday]>0,"green","red")
refer if needed
PowerBI Abstract Thesis: How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4
Hi, Anonymous
According to your description, I think you need to establish the relationship between months first, then you can put the two table fields in one table visual, and create a measure to mark red by using conditional format.
Like this:
Measure = VAR a = SUMX ( FILTER ( ALL ( Table1 ), [Ref Month] = SELECTEDVALUE ( Table2[Month] ) ), [Weight of Scrap in Kg ] ) RETURN IF ( DIVIDE ( a, SELECTEDVALUE ( Table2[Tonnage in Kg] ) ) > SELECTEDVALUE ( Table2[Target] ), "Red" )Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,Community Support Team _ Janey
