Conditional Formatting Matrix Table By Rows

Power BI Paginated Reports
1

I want to apply conditional formatting row by row based on the value of the measures. For eg. I have three measures
Measure 1 :- Sales = SUM(Sales_Data[Total Revenue])

Prev Month Sales = CALCULATE([Sales],DATEADD(‘DATE’[Date],-1,MONTH))

Prev 2MONTHS SALE = CALCULATE([Sales],DATEADD(‘DATE’[Date],-2,MONTH))

The highest value in each row should be green and the lowest value should be red and the mid value should be yellow. Attached file for your reference.File

Screenshot 2025-04-12 225657
Screenshot 2025-04-12 225657535×144 9.84 KB

and can this work if i add multiple columns as weel for eg.branch

2

If you have a Ranking Measure explicitly defined, you can achieve row-wise conditional formatting in Power BI by following these steps:

  1. Create Ranking Measures: Define three additional measures to rank the values within each row.

    • Rank_Sales = RANKX(ALLSELECTED(Sales_Data), [Sales], , DESC, DENSE)
    • Rank_PrevMonthSales = RANKX(ALLSELECTED(Sales_Data), [Prev Month Sales], , DESC, DENSE)
    • Rank_Prev2MonthsSales = RANKX(ALLSELECTED(Sales_Data), [Prev 2MONTHS SALE], , DESC, DENSE)

  2. Apply Conditional Formatting:

    • In the Matrix Visual, go to the Values section.
    • Select Conditional Formatting → Background Color.
    • Choose Rules and set conditions:
      • If Rank = 1 → Green
      • If Rank = 2 → Yellow
      • If Rank = 3 → Red

  3. Ensure Correct Sorting:

    • Make sure the ranking measures are correctly applied to each row.
    • Use ALLSELECTED() to ensure the ranking is done within the row context.

For a more detailed guide, you can check out this discussion or this tutorial. Let me know if you need further clarification! :rocket:
You can also follow the instructions in this great tutorial video:

4

I found a work-around, but it’s not working on the total row (and I don’t have time to handle that part, so I’m going to leave that to you)

First - you DO need to be dealing with a Matrix, and your visual is a table. I solved this by creating a Colum Map table for your three columns.

image
image186×107 1.98 KB

You can match the original measure names if you want, this is just what I chose.

Next - you need a measure to map the three sales measures over to this new table:

Sales Amount = 
VAR _SelectedColumn = SELECTEDVALUE( 'Matrix Column Map'[Column] )
RETURN
    SWITCH( TRUE(), 
        _SelectedColumn = "Current Month", [Sales], 
        _SelectedColumn = "Prev Month", [Prev Month Sales],
        _SelectedColumn = "2 Months Prior", [Prev 2MONTHS SALE])

then, you need a measure to handle the formatting (I actually created three - but you’ll need only one or two depending on how you choose to handle the conditional format rule)

if you want to handle the formatting like this:

image
image718×411 10.9 KB

Create the Color Code (Nums) measure

if you want to handle the formatting like this:

image
image640×335 6.88 KB

Create two measuers, one for the background (Color Code Hex), and one for the font (Font Color Hex). Personally this second method is my preference, because I can use it in multiple tables if needed and only update the measure if I want the color to change.

Color Code (Hex) = 
VAR _MaxValue = 
    MAXX( 
        { [Sales], [Prev 2MONTHS SALE], [Prev Month Sales] }, 
        [Value] )
VAR _MinValue = 
    MINX(
        { [Sales], [Prev 2MONTHS SALE], [Prev Month Sales] },
        [Value] )
RETURN
    SWITCH( TRUE(),
        [Sales Amount] = _MaxValue, "#32CD32",
        [Sales Amount] = _MinValue, "#CE142E", 
        "#FFD700")

the formulas are similar, so only showing one here.
and your outcome is:

image
image377×147 8.18 KB

both versions are demonstrated in the attached file.
eDNA Solution - Matrix Conditional Row Format.pbix (337.0 KB)

1 Like