Conditional format matrix row values based on values in another row

[Mmhoc]

Hi I have a matrix visual with 3 different measures used in the values.  I want to conditionally format the second and third row values based on whether they are higher or lower than the first row value, so it is working a bit like a KPI card I suppose.  Is this possible?

[v-hjannapu]

Hello @Mmhoc,

I would also take a moment to thank @DataNinja777 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[v-hjannapu]

Hello @Mmhoc,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Harshitha.

[DataNinja777 · Accepted solution]

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,

[Mmhoc]

Hi thank you so much for taking the time to help here, that was really useful and did the trick for me!

It did also prompt me to remember that I could simplify it by just having a measure to compare the two values (in this case, literally just [Visits] - [6wkvisits]) and then use the conditional formatting options within power bi to set up a rule based on whether that measure returns a negative or positive value.