I want to color specific cells that is diagonal of a matrix.

[prasadswamy]

I want to color specific cells that is diagonal of a matrix. As shown in 1st figure but when in a row all values zero the formula does not get apply.
Here is the formula :
conditionalformatting :
if(
   (
column = Q2 && row Q1 ||
column = Q3 && row Q1 ||
column = Q4 && row Q1 ||
column = Q3 && row Q2 ||
column = Q4 && row Q2 ||
column = Q4 && row Q3 
) && [value] = 0,
"#808080","FFFFFF"
)
In figure two when all values in rows are zero it is not getting applied to the row :

[MFelix · Accepted solution]

Hi @prasadswamy ,

Try the following code:

Condittional Format = 
			VAR OverallCalculation = CALCULATE(
					SUM('Table'[Value]),
					REMOVEFILTERS('Table'[Quarter 2])
				)
			RETURN
				SWITCH(
					TRUE(),
					((SELECTEDVALUE('Table'[Quarter 1]) = "Q1" && SELECTEDVALUE('Table'[Quarter 2]) = "Q2") ||
					(SELECTEDVALUE('Table'[Quarter 1]) = "Q1" && SELECTEDVALUE('Table'[Quarter 2]) = "Q3") ||
					(SELECTEDVALUE('Table'[Quarter 1]) = "Q1" && SELECTEDVALUE('Table'[Quarter 2]) = "Q4") ||
					(SELECTEDVALUE('Table'[Quarter 1]) = "Q2" && SELECTEDVALUE('Table'[Quarter 2]) = "Q3") ||
					(SELECTEDVALUE('Table'[Quarter 1]) = "Q2" && SELECTEDVALUE('Table'[Quarter 2]) = "Q4") ||
					(SELECTEDVALUE('Table'[Quarter 1]) = "Q3" && SELECTEDVALUE('Table'[Quarter 2]) = "Q4")) && SUM('Table'[Value]) = 0 && OverallCalculation <> 0, "#808080"
				)

[danextian · Accepted solution]

Hi @prasadswamy 

Why do you need to include zero? If the value is not blank the conditional formatting would be applied as well granting that it met the other conditions.

[Bibiano_Geraldo · Accepted solution]

Hi, @prasadswamy ,
To ensure that the conditional formatting is applied even when all values in a row are zero, you can modify your formula to include a check for rows where all values are zero. Here’s an updated version of your formula:

conditionalformatting = 
IF(
    (
        (column = "Q2" && row = "Q1") ||
        (column = "Q3" && row = "Q1") ||
        (column = "Q4" && row = "Q1") ||
        (column = "Q3" && row = "Q2") ||
        (column = "Q4" && row = "Q2") ||
        (column = "Q4" && row = "Q3")
    ) && [value] = 0,
    "#808080",
    IF(
        SUMX(
            FILTER(
                'Table',
                'Table'[row] = EARLIER('Table'[row])
            ),
            'Table'[value]
        ) = 0,
        "#808080",
        "FFFFFF"
    )
)

This should ensure that the formatting is applied correctly even when all values in a row are zero.

if this help you, please consider to mark as solution and give a Kudo.

Thank you

[danextian · Accepted solution]

Hi @prasadswamy 

Why do you need to include zero? If the value is not blank the conditional formatting would be applied as well granting that it met the other conditions.

[MFelix · Accepted solution]

Hi @prasadswamy ,

Try the following code:

Condittional Format = 
			VAR OverallCalculation = CALCULATE(
					SUM('Table'[Value]),
					REMOVEFILTERS('Table'[Quarter 2])
				)
			RETURN
				SWITCH(
					TRUE(),
					((SELECTEDVALUE('Table'[Quarter 1]) = "Q1" && SELECTEDVALUE('Table'[Quarter 2]) = "Q2") ||
					(SELECTEDVALUE('Table'[Quarter 1]) = "Q1" && SELECTEDVALUE('Table'[Quarter 2]) = "Q3") ||
					(SELECTEDVALUE('Table'[Quarter 1]) = "Q1" && SELECTEDVALUE('Table'[Quarter 2]) = "Q4") ||
					(SELECTEDVALUE('Table'[Quarter 1]) = "Q2" && SELECTEDVALUE('Table'[Quarter 2]) = "Q3") ||
					(SELECTEDVALUE('Table'[Quarter 1]) = "Q2" && SELECTEDVALUE('Table'[Quarter 2]) = "Q4") ||
					(SELECTEDVALUE('Table'[Quarter 1]) = "Q3" && SELECTEDVALUE('Table'[Quarter 2]) = "Q4")) && SUM('Table'[Value]) = 0 && OverallCalculation <> 0, "#808080"
				)