Conditional Format Not Working on Matrix Total Row

[Allen_R]

I’m having to use the following DAX code as a conditional background measure to highlight the maximum values in each row within the matrix below.  All three variables within the matrix are table columns.  In other words, there are no measures in the matrix other than the measure that’s used as the conditional format.  The matrix is filtered from both page filters (line_of_business and year) and slicers (market, vice_president, kind_of_business, and week).

The main issue I’m having is that the maximum value within the Total row is not being highlighted for some reason, despite having the conditional format applied to both Values and Totals.  Any ideas on why that might be or how to correct it?  And because of how the DAX is written, I’m also running into situations where the maximum negative value is being highlighted over a 0 value, which is incorrect.  But if I change the IF statement from “> 0” to “>= 0”, then no negative values get highlighted.

DAX code used for conditional format:

is_max_weekly_gap_to_plan = 
VAR max_positive =
    CALCULATE(
        MAXX(
            FILTER(enterprise_combined, enterprise_combined[total_sales_vs_plan] >= 0),
            enterprise_combined[total_sales_vs_plan]
        ), ALLEXCEPT(enterprise_combined, enterprise_combined[area_name], enterprise_combined[line_of_business], enterprise_combined[kind_of_business])
    )

VAR max_negative =
    CALCULATE(
        MAXX(
            FILTER(enterprise_combined, enterprise_combined[total_sales_vs_plan] < 0),
            enterprise_combined[total_sales_vs_plan]
        ), ALLEXCEPT(enterprise_combined, enterprise_combined[area_name], enterprise_combined[line_of_business], enterprise_combined[kind_of_business])
    )

VAR max_value = IF(max_positive > 0, max_positive, max_negative)

RETURN
IF(SUM(enterprise_combined[total_sales_vs_plan]) = max_value, 1, 0)

This is an example of what I'm getting.  Nothing in the Total row is getting highlighted, and I circled the 0 that should be highlighted vs. the -1 that is highlighted:

Any suggestions on how to get this to work correctly?

[Allen_R · Accepted solution]

I finally managed to get this to work as intended using the DAX code below.  My thanks to v-linyulu-msft for getting me on the right track!  As a result, I'm going to mark this post as complete.  However, I'm now trying to get the same results for a matrix that has nested rows consisting of senior_vice_president and vice_president as opposed to just area_name.  I'm hoping I can just tweak the DAX below to make that happen, but if anyone has any suggestions, it would be greatly appreciated.

is_max_gap_slt = 
VAR max_weekly_area_row = CALCULATE(MAX(enterprise_combined[total_sales_vs_plan]), FILTER(ALLSELECTED(enterprise_combined), enterprise_combined[area_name] = MAX(enterprise_combined[area_name])))
VAR max_weekly_total_row = MAXX(SUMMARIZE(ALLSELECTED(enterprise_combined), enterprise_combined[week], "_sumtotal", SUM(enterprise_combined[total_sales_vs_plan])), [_sumtotal])

RETURN

IF(ISFILTERED(enterprise_combined[area_name]), IF(SUM(enterprise_combined[total_sales_vs_plan]) = max_weekly_area_row, 1, 0), IF(SUM(enterprise_combined[total_sales_vs_plan]) = max_weekly_total_row, 1, 0))

[v-linyulu-msft]

Hi,@Allen_R 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. Below are the measure I've created for your needs:

Measure1 = 
CALCULATE(MAX('enterprise_combined'[total_sales_vs_plan]),FILTER(ALLSELECTED('enterprise_combined'),'enterprise_combined'[area_name]=MAX('enterprise_combined'[area_name])))

Measure = IF(MAX('enterprise_combined'[total_sales_vs_plan])=[Measure1],8,2)

3.Modify the display format:

4.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Allen_R]

Thank you v-linyulu-msft!  This definitely solved one of the issues that I was having which was when a maximum negative value was highlighted over a 0 value.  So at least that part is now resolved, but would you know how to highlight the maximum value in the total row? 

I selected "Values and totals" in the "Apply to" field of the conditional format just to see if your method would work for the total row, and below is what I get, which is incorrect.  Despite it being incorrect, however, your method did highlight a few numbers in the total row which is progress over my method…..which wasn’t highlighting anything.

I tried to include a sampling of the dataset, but it errored out due to size limitations....and I don't see an option to include it as an attachment.