Highlight Max value in a matrix

[nattran]

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. So the expected result is the following should be highlighted:

Please can someone help?

thanks

[Smauro · Accepted solution]

@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 · Accepted solution]

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. 

[Pragati11]

Hi @nattran ,

There is an existing tread for this issue:

https://community.powerbi.com/t5/Desktop/highlight-specific-rows-in-matrix/m-p/893600

Thanks,

Pragati

[nattran]

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 · Accepted solution]

@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.