I'm trying to use conditional formatting for the colours of a matrix. The goal is that for each row the colour of the cell will depend on it being above or below the average for that particular row.
I have managed to produce the following matrix using this DAX measure as the conditional formatting:
Conditional Formatting =
VAR SelectedMonth = CALCULATE(SUMX(Reviews_Table,Reviews_Table[Number_of_Reviews]))
VAR AveragePerCountry = [Average Reviews]
RETURN
SWITCH(
TRUE(),
SelectedMonth >= 1.5*AveragePerCountry, "#008651",
AND(SelectedMonth < 1.5*AveragePerCountry, SelectedMonth >= AveragePerCountry), "#82ac2b",
AND(SelectedMonth < AveragePerCountry, SelectedMonth >= 0.5*AveragePerCountry), "#ff8100",
SelectedMonth < 0.5*AveragePerCountry, "#FE2828" )
where [Average Reviews] measure has been defined as
Average Reviews =
VAR SUMFILTER =
CALCULATE (
SUM ( Reviews_Table[Number_of_Reviews] ),
ALLSELECTED( 'Reviews_Table' )
)
VAR COUNTFILTER =
CALCULATE (
DISTINCTCOUNT ( Reviews_Table[Number_of_Reviews] ),
ALLSELECTED( 'Reviews_Table' )
)
RETURN
DIVIDE ( SUMFILTER, COUNTFILTER )
However, as it stands, this conditional formatting seems to compare each value to the total average over all rows. Is it possible to obtain the average for each row (in this case "Market") and use that as conditional formatting? Hopefully the answer will respect filters (I have a slicer that changes the time interval, and the average should be for that specific interval).
All help would be appreciated!
PS: Just as additional info Reviews_Table has the following columns: Month, Market, Retailer (not depicted in the matrix but accessible by pressing "+") and Number_of_Reviews.