Forum Discussion
Matrix - conditional formatting when 3 values in a row are negative
Hi All!
Unfortunately I am facing an issue and literally losing my mind trying to solve it. I have a matrix table and I want to conditional format it based on values that are for last 5 ISO weeks. I have a calculated column that is giving me true when there are last 5 weeks and it is working fine and dynamically.
Then I want to see if there are 3 negaitve values in a row in the column and if this contion is true - I want to color all 3 in RED.
Firt I tried to solve only last 3 weeks coloring not the 5 one to make it easier and learn how to do this but I failed...
Those are the results I've achieved when I tried to work on last 3 weeks. Here one before last is the only one that should be colored as red, rest should be grey in this scenario. I wanted to combine all three conditions and I reached a result as 1 for the proper row but only for total.
I am new to PBI and maybe I am not understanding properly how the DAX works in such a measure. Can anyone advice something?
I would appreciate any help!
Measures I used:
TestColoringNegative =
VAR currentweek = [Current Year/Week]
VAR cw =
CALCULATE ( [Measure Value], CALENDAR[ISO Year/Week] = currentweek )
VAR pw =
CALCULATE ( [Measure Value], CALENDAR[ISO Year/Week] = currentweek - 1 )
VAR pw2 =
CALCULATE ( [Measure Value], CALENDAR[ISO Year/Week] = currentweek - 2 )
VAR valuebynegative =
SWITCH ( TRUE (), cw < 0, 1, pw < 0, 1, pw2 < 0, 1, 0 )
RETURN
valuebynegative
TestColoringNegative2 =
VAR currentweek = [Current Year/Week]
VAR cw =
CALCULATE ( [Measure Value], CALENDAR[ISO Year/Week] = currentweek )
VAR pw =
CALCULATE ( [Measure Value], CALENDAR[ISO Year/Week] = currentweek - 1 )
VAR pw2 =
CALCULATE ( [Measure Value], CALENDAR[ISO Year/Week] = currentweek - 2 )
RETURN
IF ( cw < 0 && pw < 0 && pw2 < 0, 1, 0 )
Thanks,
Greg
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
4 Replies
- lbendlin
Super User
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
Ibendlin,
is this something what you meant with virtual table?
I used this summarize with add column in a measure but for a meantime I put is just as a table to see how it works.
Is it possible somehow to limit the "1" that appears, when the sum of it is based on a product where the SUM is at least greater than 3 (I mean 1 should be only where I put the red rectangle rest should be 0).
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
