Matrix, Cell Element, Background Conditional Formatting - don't apply to column totals

[jimbob2285]

I have a matrix, whose rows show types of quotes with a total quotes below (row totals) and the columns are lead times and the values are the number of quotes

I want to conditionally format columns that are past their lead time in red and those that are not yet due in green.  I can;t apply conditional formatting to specific columns because it's a matrix, not a table - So I'm applying background conditional formatting to cell elements instead.  I'm also applying them to Values & Totals so that the row totals at the bottom of the grid are also colour coded the same.

Which seems to be working fine, until we look at the column totals... and the subtotals are conditionally formatted, which is fine, but so is the grand total, and I don't want it to be - but I can't find a way not to have the grand totals unformatted, as they're currently green, indicating it's a good number, when in fact it's a combination of a bad and good number (bad being overdue, good being not yet due)

It's probably easier to look at the images below - it;s the grand total on the far RHS ofthe matrix, that I don;t want conditionally formatted... and what decided it should be green, rather than red:

[AndrewGould · Accepted solution]

Hi,

Can you do this with a measure?  Here's a basic example:

Cell Colour = SWITCH(

    TRUE(),

    SELECTEDVALUE('QCD Period L2'[QCD Period L2]) = "Today", "Yellow",

    SELECTEDVALUE('QCD Period L2'[QCD Period L2]) = "1 Month + Past QCD", "Grey",

    CONTAINSSTRING(SELECTEDVALUE('QCD Period L2'[QCD Period L2]), "-"), "Red",

    CONTAINSSTRING(SELECTEDVALUE('QCD Period L2'[QCD Period L2]), "+"), "Green",

    SELECTEDVALUE('QCD Days'[QCD Days]) = "Past QCD", "Red",

    SELECTEDVALUE('QCD Days'[QCD Days]) = "Due", "Green"

)

You could then assign this to the Background Color conditional format rather than using the rules you defined

And the result (sorry about the gross colours!):

[jimbob2285]

Thanks Andrew, I thought about using a measure, but couldn't see how it would produce a different result - I would have never got there without your help and didn;t realise you could apply the measure to different levels within the matrix

[AndrewGould]

Excellent, happy to hear that it helped!