MAX OF A MEASURE IN CONDITIONAL FORMATTING

Hi corbs ,

To highlight the highest value for the YTD Metric in your matrix visual, you can use a combination of measures to calculate the maximum value dynamically and apply conditional formatting based on it. The first step is to create a measure that determines the maximum YTD Metric value for each combination of year, month, and job title. This requires ignoring the current cell's filter context while maintaining the grouping structure.

Here’s a measure to calculate the maximum value for each group:

MaxValueForGroup =
VAR CurrentYear = SELECTEDVALUE('Calendar'[Year])
VAR CurrentMonth = SELECTEDVALUE('Calendar'[Month])
VAR CurrentJobTitle = SELECTEDVALUE('Fact'[Job Title])
RETURN
CALCULATE(
    MAXX(
        SUMMARIZE(
            ALL('Fact'),
            'Calendar'[Year],
            'Calendar'[Month],
            'Fact'[Job Title],
            "YTDValue", [YTD Metric]
        ),
        [YTDValue]
    ),
    'Calendar'[Year] = CurrentYear,
    'Calendar'[Month] = CurrentMonth,
    'Fact'[Job Title] = CurrentJobTitle
)

This measure uses SUMMARIZE to group the data by year, month, and job title, calculating the maximum YTD Metric within those groups. The ALL function ensures the calculation considers the entire dataset, ignoring the filter context of the matrix visual.

Next, create a measure to determine whether the current cell's YTD Metric equals the maximum value for its group. This measure will be used for conditional formatting:

Highlight Max =
IF(
    [YTD Metric] = [MaxValueForGroup],
    1,
    0
)

This measure compares the YTD Metric for the current cell to the calculated maximum value for the group. If the values match, it returns 1; otherwise, it returns 0.

To apply conditional formatting in the matrix visual, use the Highlight Max measure. Select the matrix visual, navigate to the YTD Metric field, and apply conditional formatting based on rules. Configure the rule so that a value of 1 corresponds to a specific background or font color (e.g., green for highlighting).

This approach calculates the maximum dynamically, respects the required grouping, and ensures efficient performance. If your dataset is particularly large, additional optimizations, such as pre-aggregating data in the fact table, may improve performance.

Best regards,