Forum Discussion
Matrix - conditional formatting when 3 values in a row are negative
Hi Anonymous ,
Regardless the weeknum, you could create a measure to count the negative value for each row.
_count = CALCULATE(DISTINCTCOUNT('Table'[column]),FILTER(ALLSELECTED('Table'),'Table'[row]=SELECTEDVALUE('Table'[row])&&[Measure]<0))Then create the condition measure based the count value.
color = IF([Measure]<0&&[_count]>=3,"red")In your case, you could add weeknum condition to the first formula.
Best Regards,
Jay
It's much more complex than this. You will need to use temporary tables in your measure.
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with.
Hi Ibendlin,
thank you for your answer.
I made some random data in xlsx and put it into pbix.
| ISO Week | Product | Plan | Actual | Diff | Value 1 |
| 01.2022 | A | 123 | 56 | 67 | 1,84% |
| 01.2022 | B | 345 | 324 | 21 | 16,43% |
| 01.2022 | C | 433 | 564 | -131 | -3,31% |
| 02.2022 | A | 543 | 543 | 32 | 16,97% |
| 02.2022 | B | 312 | 232 | 80 | 3,90% |
| 02.2022 | C | 765 | 567 | 198 | 3,86% |
| 03.2022 | A | 887 | 876 | 11 | 80,64% |
| 03.2022 | B | 123 | 545 | -422 | -0,29% |
| 03.2022 | C | 423 | 123 | 300 | 1,41% |
| 04.2022 | A | 543 | 675 | -132 | -4,11% |
| 04.2022 | B | 6554 | 4345 | 2209 | 2,97% |
| 04.2022 | C | 6542 | 2345 | 4197 | 1,56% |
| 05.2022 | A | 1302 | 430 | 872 | 1,49% |
| 05.2022 | B | 234 | 0 | 234 | 1,00% |
| 05.2022 | C | 3215 | 4321 | -1106 | -2,91% |
| 07.2022 | A | 4032 | 3213 | 819 | 4,92% |
| 07.2022 | B | 4326 | 7655 | -3329 | -1,30% |
| 07.2022 | C | 5433 | 6575 | -1142 | -4,76% |
| 06.2022 | A | 4234 | 6457 | -2223 | -1,90% |
| 06.2022 | B | 7565 | 5436 | 2129 | 3,55% |
| 06.2022 | C | 7655 | 8766 | -1111 | -6,89% |
| 08.2022 | A | 8764 | 4444 | 4320 | 2,03% |
| 08.2022 | B | 2345 | 1436 | 909 | 2,58% |
| 08.2022 | C | 1234 | 653 | 581 | 2,12% |
Most of this values (especially the % ones) doesn't make any sense but we reach here a sample that we have 3 week in a row negative values so as a sample it is pretty ok I think.
Unfortunately I cannot paste pbix file here with relation and measures in calendar table 😞
Best regards,
Greg