Forum Discussion
Conditional Formatting in a Matrix with Multiple Levels
- 4 years ago
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.
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.
That worked like a charm! Thank you so much for your help. Your support is very appreciated!!!