Conditional Format Largest Value in Each Row Within Matrix Visual

[Allen_R]

The following matrix shows the sales_vs_plan for each week so far this year by market_area and highlights the largest value in each row.  I'm trying to duplicate a similar visual in Power BI but have had no luck in getting the conditional format to work correctly.

This is a sample table that I used to generate the maxtrix above in Excel.  The actual table in Power BI has multiple years and weeks as well as additonal columns, lines of business, market areas, and more.  I have a page filter for year (2025) and line_of_business (Retail).

year	week	line_of_business	market_area	vpa	area_name	sales_vs_plan
2025	1	Retail	Eastern	Luke Skywalker	NY - New York City	1100
2025	2	Retail	Eastern	Luke Skywalker	NY - New York City	-50
2025	1	Retail	Eastern	Luke Skywalker	NY - Buffalo	99
2025	2	Retail	Eastern	Luke Skywalker	NY - Buffalo	76
2025	1	Retail	Eastern	Luke Skywalker	CT - Hartford	-1500
2025	2	Retail	Eastern	Luke Skywalker	CT - Hartford	-501
2025	1	Retail	Eastern	Luke Skywalker	NY - Albany	-66
2025	2	Retail	Eastern	Luke Skywalker	NY - Albany	82
2025	1	Retail	Eastern	Tom Brady	ME - Portland	788
2025	2	Retail	Eastern	Tom Brady	ME - Portland	35
2025	1	Retail	Eastern	Tom Brady	VT - Burlington	25
2025	2	Retail	Eastern	Tom Brady	VT - Burlington	-5
2025	1	Retail	Eastern	Tom Brady	NH - Concord	202
2025	2	Retail	Eastern	Tom Brady	NH - Concord	221
2025	1	Retail	Eastern	Tom Brady	MA - Boston	1007
2025	2	Retail	Eastern	Tom Brady	MA - Boston	-1102
2025	1	Retail	Southeast	Arnold Schwarzenegger	FL - Miami	801
2025	2	Retail	Southeast	Arnold Schwarzenegger	FL - Miami	3500
2025	1	Retail	Southeast	Arnold Schwarzenegger	FL - Orlando	2100
2025	2	Retail	Southeast	Arnold Schwarzenegger	FL - Orlando	2300
2025	1	Retail	Southeast	Arnold Schwarzenegger	FL - Tampa	-1507
2025	2	Retail	Southeast	Arnold Schwarzenegger	FL - Tampa	-781
2025	1	Retail	Southeast	Arnold Schwarzenegger	FL - Jacksonville	92
2025	2	Retail	Southeast	Arnold Schwarzenegger	FL - Jacksonville	1375
2025	1	Retail	Southeast	Tyrion Lannister	GA - Atlanta	1671
2025	2	Retail	Southeast	Tyrion Lannister	GA - Atlanta	1011
2025	1	Retail	Southeast	Tyrion Lannister	GA - Savannah	404
2025	2	Retail	Southeast	Tyrion Lannister	GA - Savannah	781
2025	1	Retail	Southeast	Tyrion Lannister	SC - Charleston	22
2025	2	Retail	Southeast	Tyrion Lannister	SC - Charleston	23
2025	1	Retail	Southeast	Tyrion Lannister	NC - Charlotte	401
2025	2	Retail	Southeast	Tyrion Lannister	NC - Charlotte	475
2025	1	Retail	Southeast	Oprah Winfrey	AL - Montgomery	-57
2025	2	Retail	Southeast	Oprah Winfrey	AL - Montgomery	-91
2025	1	Retail	Southeast	Oprah Winfrey	AL - Huntsville	88
2025	2	Retail	Southeast	Oprah Winfrey	AL - Huntsville	61
2025	1	Retail	Southeast	Oprah Winfrey	TN - Nashville	104
2025	2	Retail	Southeast	Oprah Winfrey	TN - Nashville	223
2025	1	Wholsale	Southeast	Oprah Winfrey	TN - Knoxville	1117
2025	2	Wholsale	Southeast	Oprah Winfrey	TN - Knoxville	1008

My current DAX formula below highlights the total row correctly but only highlights the largest value out of all the "market_area".  In other words, only one single value is getting highlighted within the body of the matrix and it happens to be the largest value out of all the values in the body of the matrix.  Would anyone have any ideas on how I could adjust the "max_weekly_area_row" variable so that it works for each market_area row independently?  Much thanks in advance!!

is_max_gap_ma =
VAR current_value = SUM(enterprise_combined[total_production_vs_plan])
VAR max_weekly_area_row = MAXX(SUMMARIZE(ALLSELECTED(enterprise_combined), enterprise_combined[market_area], enterprise_combined[week], "_sumtotal", SUM(enterprise_combined[sales_vs_plan])), [_sumtotal])
VAR max_weekly_total_row = MAXX(SUMMARIZE(ALLSELECTED(enterprise_combined[market_area], enterprise_combined[week]), enterprise_combined[week], "_sumtotal", SUM(enterprise_combined[sales_vs_plan])), [_sumtotal])

RETURN

IF(
ISINSCOPE(enterprise_combined[market_area]),
IF(current_value = max_weekly_area_row, 1, 0), // Highlight if it matches the max for the market area
IF(current_value = max_weekly_total_row, 1, 0) // Highlight if it matches the max for the total row
)

[Ritaf1983]

Hi @Allen_R 
You can use a dax formula like 

max_ value =

var max_area = CALCULATE(MAXX(SUMMARIZE('Table','Table'[week],'Table'[market_area],"sum",[sales_Vs_plan_]),[sum]),ALLSELECTED('Table'[week]))

var max_total = CALCULATE(MAXX(SUMMARIZE('Table','Table'[week],"sum",[sales_Vs_plan_]),[sum]),ALLSELECTED('Table'[week]))

RETURN

if (HASONEFILTER('Table'[market_area]),

   if([sales_Vs_plan_]=max_area,"Light Green"),

   if([sales_Vs_plan_]=max_total,"Light Green"))

+ 
Note that you are using the modifications of values and totals at the conditional formatting:

The result:

Pbix is attached

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

[Greg_Deckler · Accepted solution]

@Allen_R Try this:

Measure 2 = 
    VAR __Market = MAX('Table6'[market_area])
    VAR __Value = SUM( 'Table6'[sales_vs_plan] )
    VAR __Table = SUMMARIZE( FILTER( ALL( 'Table6' ), [market_area] = __Market ), [week], "__Value", SUM( Table6[sales_vs_plan]) )
    VAR __Max = MAXX( __Table, [__Value] )
    VAR __Result = IF( __Value = __Max, "#00FF00", "" )
RETURN
    __Result

[Allen_R]

Greg....you are the man!!  Using the FILTER function in conjunction with MAX for market_area did the trick!  I just modified my original code with your FILTER/MAX suggestion, and it works perfectly!  Below is the final DAX code that works as needed.  Thank so much!!

is_max_gap_ma =

VAR current_value = SUM(enterprise_combined[sales_vs_plan])

VAR max_weekly_area_row = MAXX(SUMMARIZE(FILTER(ALLSELECTED(enterprise_combined), [market_area] = MAX(enterprise_combined[market_area])), enterprise_combined[week], "_sumtotal", SUM(enterprise_combined[sales_vs_plan])), [_sumtotal])

VAR max_weekly_total_row = MAXX(SUMMARIZE(ALLSELECTED(enterprise_combined[market_area], enterprise_combined[week]), enterprise_combined[week], "_sumtotal", SUM(enterprise_combined[sales_vs_plan])), [_sumtotal])

RETURN

IF(

    ISINSCOPE(enterprise_combined[market_area]),  

    IF(current_value = max_weekly_area_row, 1, 0),  // Highlight if it matches the max for the market area

    IF(current_value = max_weekly_total_row, 1, 0)  // Highlight if it matches the max for the total row

)