Forum Discussion

nattran's avatar
nattran
Helper I
6 years ago
Solved

Highlight Max value in a matrix

Hi,   I've created a measure Avg Sales and below is the matrix I displayed the data:   Now Im trying to using the conditional formatting to highlight the max value per Department per Level....
Need Help
  • Smauro's avatar
    Smauro
    6 years ago

    nattran, well it's DAX you're asking for.

    Haven't really tested, but based on your image something like this should work:

     

    maxSum =
    VAR m =
        CALCULATE (
            MAXX ( SUMMARIZE ( 'table', 'table'[Dep] ), CALCULATE ( SUM ( 'table'[Amt] ) ) ),
            REMOVEFILTERS ( 'table'[Dep] )
        )
    VAR s =
        SUM ( 'table'[Amt] )
    RETURN
        IF ( m = s, 1 )

     

    and then conditionally filter SUM('table'[Amt]) when maxSum = 1 then colour.

  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi nattran ,

     

    You could set the highlight in conditional formatting with a color measure( [Measure] is the value in matrix ).

    FormatMeasure =
VAR a =
    MAXX ( ALLEXCEPT ( 'Table', 'Table'[Column1] ), [Measure] )
RETURN
    IF ( [Measure] = a, "yellow" )

    Here is my test result and test file for your reference. 

     

nattran's avatar
nattran
Helper I
6 years ago

Thanks. But in this case, I need to find dynamic MAX sales value per department per level based on the time period selection. How can I achieve that?

 

thanks 

  • Smauro's avatar
    Smauro
    Solution Sage
    6 years ago

    nattran, well it's DAX you're asking for.

    Haven't really tested, but based on your image something like this should work:

     

    maxSum =
    VAR m =
        CALCULATE (
            MAXX ( SUMMARIZE ( 'table', 'table'[Dep] ), CALCULATE ( SUM ( 'table'[Amt] ) ) ),
            REMOVEFILTERS ( 'table'[Dep] )
        )
    VAR s =
        SUM ( 'table'[Amt] )
    RETURN
        IF ( m = s, 1 )

     

    and then conditionally filter SUM('table'[Amt]) when maxSum = 1 then colour.