Conditional Formatting a matrix

[msimmonsmcse]

I have a matrix viz that shows revenue per year, one year for each column, with customers listed in each row. I applied a conditional format (or so I thought) to each cell based on the revenue number. (Cell Elements>Background Color). I was expecting to get a color gradient for each cell, applied row by row, so each client would show their high and low years of revenue filled with the definded background colors for high and low values. But the behavior I am getting is that the format applies the high and low color to the highest/lowest cell in the entire matrix. How do I get the high and low values for each row to show the proper colors?

Below is a screen shot with the customer names redacted. 

[v-jingzhang · Accepted solution]

Hi @msimmonsmcse 

Here is another workaround. When your data table is like below (if not, you can summarize company, year and revenue to get a table like below), 

You can create a Rank column to rank revenues in different years among every company. 

Rank = 
RANKX (
    FILTER ( 'Table', 'Table'[Company] = EARLIER ( 'Table'[Company] ) ),
    [Revenue],
    ,
    ASC,
    DENSE
)

Add another color column based on rank values. You can also create a similar measure for conditional formatting. 

Color = SWITCH([Rank],1,"#ABEBC6",2,"#F9E79F",3,"#FAD7A0",4,"#EDBB99")

Format the background color based on Field value > Color column. 

Hope this helps. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[ebeery]

@msimmonsmcse achieving what you're describing is not terribly straightforward, and probably is not possible using the built-in conditional formatting options.

However, the below video describes an approach to achieve this effect using some conditional formatting measures.

UNLEASH the full POTENTIAL of Conditional Formatting | ROW by ROW color scale in a MATRIX in PowerBI