Report Builder 3.0 (SSRS) – Highlight Row in Matrix Based on Max Value in Column Group

I have been tasked with writing a report with a matrix that categorizes and groups data by rows and columns, and I’m told that each row must be a certain color based on which column group the maximum count appears in.

So I created my matrix. On the rows, it is grouped first by state and then by city, and it has a total row at the bottom. The column group is based on two calculated fields I created. Here are their labels and expressions:

DaysSinceLastUpdate

=DateDiff(DateInterval.Day,Fields!RECORD_UPDATED_DATE.Value,Today())

DaysSinceUpdateGrouping

=Switch(Fields!DaysSinceLastUpdate.Value<91,"Less Than 90 Days", Fields!DaysSinceLastUpdate.Value<181,"91-180 Days", Fields!DaysSinceLastUpdate.Value<365,"181-365 Days", Fields!DaysSinceLastUpdate.Value>=365,"More Than 1 Year")

So my table looks like this:

When the report runs, a user selects one or more states using a parameter. If a user picked just Oregon, the results might look something like this:

What I want to do is make each entire row change color based on where the maximum value appears. For Portland, the highest value is in the “181-365” days column, so I want that row orange. For Eugene, the highest value is in the “More Than 1 Year” column, so I want that row red. For Bend, the highest value is in the “Less Than 90 Days” column, so I want that row green. Both the Oregon and Total rows should also be green. (If the “91-180 Days” column had the maximum value, its associated row should be yellow.)

I selected the cells I wanted to apply the background color rule to, and I applied this expression for the Fill property:

=switch(max(Fields!DaysSinceUpdateGrouping.Value, "City")="Less Than 90 Days","LimeGreen", max(Fields!DaysSinceUpdateGrouping.Value, "City")="91-180 Days","Yellow",   max(Fields!DaysSinceUpdateGrouping.Value, "City")="181-365 Days","Orange", max(Fields!DaysSinceUpdateGrouping.Value, "City")="More Than 1 Year","Tomato")

But the row always turns red.

Any idea what I’m doing wrong? How can I tell the Fill expression to find the maximum value in the column group for each row?

I got it! I had to add a row group for DaysSinceUpdateGrouping in addition to the column group. Then I deleted the column that appeared but left the group in place. That way, in my Switch expression for the cell color, I was able to replace “City” with “DaysSinceUpdateGrouping1” so that the reference was calculating the details, not the City total.