Forum Discussion

Allen_R's avatar
Allen_R
Frequent Visitor
1 year ago
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 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).

yearweekline_of_businessmarket_areavpaarea_namesales_vs_plan
20251RetailEasternLuke SkywalkerNY - New York City1100
20252RetailEasternLuke SkywalkerNY - New York City-50
20251RetailEasternLuke SkywalkerNY - Buffalo99
20252RetailEasternLuke SkywalkerNY - Buffalo76
20251RetailEasternLuke SkywalkerCT - Hartford-1500
20252RetailEasternLuke SkywalkerCT - Hartford-501
20251RetailEasternLuke SkywalkerNY - Albany-66
20252RetailEasternLuke SkywalkerNY - Albany82
20251RetailEasternTom BradyME - Portland788
20252RetailEasternTom BradyME - Portland35
20251RetailEasternTom BradyVT - Burlington25
20252RetailEasternTom BradyVT - Burlington-5
20251RetailEasternTom BradyNH - Concord202
20252RetailEasternTom BradyNH - Concord221
20251RetailEasternTom BradyMA - Boston1007
20252RetailEasternTom BradyMA - Boston-1102
20251RetailSoutheastArnold SchwarzeneggerFL - Miami801
20252RetailSoutheastArnold SchwarzeneggerFL - Miami3500
20251RetailSoutheastArnold SchwarzeneggerFL - Orlando2100
20252RetailSoutheastArnold SchwarzeneggerFL - Orlando2300
20251RetailSoutheastArnold SchwarzeneggerFL - Tampa-1507
20252RetailSoutheastArnold SchwarzeneggerFL - Tampa-781
20251RetailSoutheastArnold SchwarzeneggerFL - Jacksonville92
20252RetailSoutheastArnold SchwarzeneggerFL - Jacksonville1375
20251RetailSoutheastTyrion LannisterGA - Atlanta1671
20252RetailSoutheastTyrion LannisterGA - Atlanta1011
20251RetailSoutheastTyrion LannisterGA - Savannah404
20252RetailSoutheastTyrion LannisterGA - Savannah781
20251RetailSoutheastTyrion LannisterSC - Charleston22
20252RetailSoutheastTyrion LannisterSC - Charleston23
20251RetailSoutheastTyrion LannisterNC - Charlotte401
20252RetailSoutheastTyrion LannisterNC - Charlotte475
20251RetailSoutheastOprah WinfreyAL - Montgomery-57
20252RetailSoutheastOprah WinfreyAL - Montgomery-91
20251RetailSoutheastOprah WinfreyAL - Huntsville88
20252RetailSoutheastOprah WinfreyAL - Huntsville61
20251RetailSoutheastOprah WinfreyTN - Nashville104
20252RetailSoutheastOprah WinfreyTN - Nashville223
20251WholsaleSoutheastOprah WinfreyTN - Knoxville1117
20252WholsaleSoutheastOprah WinfreyTN - Knoxville1008

 

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
)

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    1 year ago

    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

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    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's avatar
      Allen_R
      Frequent Visitor
      1 year ago

      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
      )
  • Ritaf1983's avatar
    Ritaf1983
    Super User
    1 year ago

    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.