Conditional Formatting in a Matrix with Multiple Levels

[whenriques]

Do you happen to know how to use conditional formatting to highlight the highest monthly value? See below:

I tried the DAX measure below:

Then, going to conditional formatting and using the following:

I modified the DAX to use just Year and it works, but the challenge is when I put Month and Year on the same matrix.

Do you have any ideas or thoughts?

Thanks in advance for taking a look at it. I appreciate your time and effort.

Cheers,

Willer

[amitchandak]

@whenriques , Change _MaxUnits like

var _MaxUnits = calculate (

maxx(values( Date[Month Year]), [ContMargin]) , allselected() )

or

var _MaxUnits = calculate (

maxx(values( Date[Month Year]), [ContMargin]) , allselected( Date[Year]) )

[whenriques]

Also, I wanted to mention that the matrix has the values switch to rows under Format visual. Is that what might be causing the problem? See below:

Thanks again for your help!

[whenriques]

Thank you so much for your response @amitchandak . By using the recommended DAX, It highlighted the highest value of all years and months.

Currently, I have 'Date'[Year] as rows and 'Date'[Month Name] as columns. The goal is to highlight the highest 'Date'[Month Name] of every year. Seems that we are so close... Do you have any thoughts or suggestions?

I appreciate your time and effort!

Regards,

Willer

[MarkLaf · Accepted solution]

Does the below work for you? I think the piece you are missing is that you do want to still filter by years, which you can accomplish by adding VALUES( Date[Year] ) as a filter argument of CALCULATE. The ALLSELECTED was making it so all filters from Date were getting removed. E.g.

MaxInRowContMargin = 
VAR __MaxUnits =
CALCULATE( 
    MAXX( SUMMARIZE( 'Date', 'Date'[Year], 'Date'[Month] ), [ContMargin] ),
    REMOVEFILTERS( 'Date' ), VALUES( 'Date'[Year] ) 
)
RETURN
IF(
    [ContMargin] = __MaxUnits,
    "Light Blue"
)

Note that I summarized Date by Year and Month columns, but I believe the above should work with your VALUES( [Month Year] ) instead of a summarize, too.

[whenriques]

That worked like a charm! Thank you so much for your help. Your support is very appreciated!!!