Matrix - conditional formatting when 3 values in a row are negative

[gregsuash]

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:

Current Year/Week =

LOOKUPVALUE (

'CALENDAR'[ISO Year/Week],

CALENDAR[Date], TODAY () - 7

) -- I used -7 because I want to have "current week" as last finished week which is week 6.

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

[Anonymous · Accepted solution]

Hi @gregsuash ,

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

[lbendlin]

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. 

[gregsuash]

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).

[gregsuash]

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