Forum Discussion
Solved
Conditional Format Largest Value in Each Row Within Matrix Visual
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...
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 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
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
)