Conditional format matrix row values based on values in another row

Hi Mmhoc ,

Yes, what you're asking for is a common and powerful feature in Power BI. The best way to accomplish this is by creating a special DAX measure that defines the color logic. You will then instruct the visual to use the output of this measure to set the background color of the cells.

First, you'll need to create a new measure that compares 6wkvisits to Visits and returns a color code. You can do this by navigating to the Home tab and clicking New Measure. Then, enter the following DAX formula. This code will return a dark green hex code if the value is higher and a red one if it's lower.

Format 6wkvisits = 
VAR VisitsValue = [Visits]
VAR SixWeekValue = [6wkvisits]
RETURN
    IF(SixWeekValue > VisitsValue, "#107C41", "#D32F2F")

Similarly, you'll create another measure to handle the formatting for the Cv_Av_Visits row. This measure follows the same logic, comparing its value against the Visits value for the same column.

Format Cv_Av_Visits = 
VAR VisitsValue = [Visits]
VAR CvAvValue = [Cv_Av_Visits]
RETURN
    IF(CvAvValue > VisitsValue, "#107C41", "#D32F2F")

Once you have created these measures, you can apply the formatting. Select your matrix visual and go to the Visualizations pane. Under the Format your visual tab (the paintbrush icon 🖌️), expand the Cell elements section. In the Apply settings to dropdown, choose the 6wkvisits measure. Turn on the Background color toggle and click the fx button next to it. In the dialog box that opens, change the Format style to Field value and then, for What field should we base this on?, select your Format 6wkvisits measure. Click OK.

You will then repeat this exact process for the Cv_Av_Visits row, making sure to select Cv_Av_Visits in the dropdown and choosing your Format Cv_Av_Visits measure as the field value. This will complete the setup, and your matrix rows will now be dynamically colored to reflect their performance against the Visits baseline.

Best regards,