How to apply conditional formatting by column ranking in a matrix?

[MattiaFratello]

Hi everyone,

Is it possible to set up conditional formatting in a Power BI matrix so that:

• For each offset column (e.g., 0, 1, 2, ...), the highest percentage value for that column is colored with the darkest shade (e.g., dark blue),
• All other percentages in that column are shaded in descending order, lighter for lower values,
• The formatting should work independently for each offset, so every column highlights its own max value rather than comparing values across columns.

I want the color to reflect the relative ranking or proportion within each offset column, not the entire matrix.

Has anyone accomplished this, or does anyone have recommended DAX/visual setup tips for this type of column-wise dynamic coloring?

Thanks in advance!

[v-tejrama · Accepted solution]

Hi  @MattiaFratello ,

Thanks for reaching out to the Microsoft fabric community forum.

You have correctly set up the measures using ALLEXCEPT to calculate the column wise maximum, and normalizing each value accordingly is essential for enabling conditional formatting per column. Applying gradient formatting to the normalized measure ensures that the highest value in each column is distinctly highlighted, rather than comparing values across the entire matrix.

This method effectively addresses the original requirement and will be valuable for others with similar needs. Thank you again for your clear demonstration and contribution.

Please find the attached PBIX andScreenshort file for your reference.

Best regards,
Tejaswi.
Community Support Team

[GrowthNatives]

Hi @MattiaFratello ,
i see that you want column-wise conditional formatting in a Power BI matrix so that:

• Each column highlights its own maximum value darkest,

• The rest fade proportionally,

• No cross-column comparison (independent scale per column).

Here’s how I would do it:

1. Create a Max per Column Measure

You need to calculate the maximum value per column (offset).
Assume your base measure is [Percentage].

MaxPerColumn =
CALCULATE (
    MAX ( 'Table'[Percentage] ),
    ALLEXCEPT ( 'Table', 'Table'[OffsetColumn] )
)

• OffsetColumn is your column identifier (0,1,2,... in your screenshot).

• ALLEXCEPT ensures the max is calculated only within that column.
  
  

2. Create a Normalized Value Measure

This measure scales each value between 0 and 1 within its own column.

NormalizedValue =
DIVIDE ( [Percentage], [MaxPerColumn], 0 )

So the maximum in each column = 1, all others are fractions.

3. Use NormalizedValue for Conditional Formatting

• Select your matrix visual.

• Go to Format → Conditional formatting → Background color / Font color.

• Choose Format by → Field value.

• Pick the measure [NormalizedValue].

• Then apply a Color gradient (e.g., Dark blue → Light blue).
  
  

4. Optional: Use RANK Instead of Scaling

If you prefer discrete ranks (top = darkest, 2nd lighter, etc.):

RankPerColumn =
RANKX (
    ALLSELECTED ( 'Table'[RowID] ),
    [Percentage],
    ,
    DESC,
    Dense
)

Then assign colors by rule (Rank = 1 → Darkest, Rank = 2 → Medium, etc.).

5. Result

• Each column is shaded independently,

• Top performer in each column gets darkest shade,

• Lower ones get progressively lighter,

• No cross-column influence.

This approach works for Matrix visuals with dynamic columns, as long as you have a column identifier (Offset, Month, etc.).

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]

[MattiaFratello]

I've tried similar approaches but wasn't able to let them work.

My DAX for the % is as follow:

Daily REG Cohort Metric = 
VAR SelectedMetric = SELECTEDVALUE('Cohort Metric Pick'[Metric])

VAR __cohort_perc = 
SWITCH(
        TRUE(),
        SelectedMetric = "Actives", 
        DIVIDE([Daily Abs Actives], [Daily New Registrations]),
        SelectedMetric = "Depositors",
        DIVIDE([Daily Abs Depositors], [Daily New Registrations]),
        SelectedMetric = "Withdrawers",
        DIVIDE([Daily Abs Withdrawers], [Daily New Registrations])
    )

VAR inScope = ISINSCOPE('[Dim] Calendar_REG'[Date])

VAR OffsetValue = SELECTEDVALUE('[Fact] Daily Cohort'[daily_offset_reg])

VAR metric_with_offset =
SWITCH(
    TRUE(),
    SelectedMetric = "Actives",
    CALCULATE(
        [Daily Abs Actives],
        FILTER(
            ALLSELECTED('[Fact] Daily Cohort'),
            '[Fact] Daily Cohort'[daily_offset_reg] = OffsetValue
        )
    ),
    SelectedMetric = "Depositors",
    CALCULATE(
        [Daily Abs Depositors],
        FILTER(
            ALLSELECTED('[Fact] Daily Cohort'),
            '[Fact] Daily Cohort'[daily_offset_reg] = OffsetValue
        )
    ),
    SelectedMetric = "Withdrawers",
    CALCULATE(
        [Daily Abs Withdrawers],
        FILTER(
            ALLSELECTED('[Fact] Daily Cohort'),
            '[Fact] Daily Cohort'[daily_offset_reg] = OffsetValue
        )
    ))


VAR regs_with_offset =
    CALCULATE(
        SUM('[Fact] Daily Registrations'[new_registrations]),
        FILTER(
            ALLSELECTED('[Fact] Daily Cohort'),
            '[Fact] Daily Cohort'[daily_offset_reg] = OffsetValue
        )
    )
VAR weighted_total_perc = DIVIDE(metric_with_offset, regs_with_offset)
RETURN
    IF(
        inScope,
        __cohort_perc,
        weighted_total_perc
    )

[Shahid12523]

Create a DAX measure that ranks values within each column using RANKX.
Normalize the rank to get a value between 0 and 1.
Use that normalized measure in Conditional Formatting > Background color.
Choose Format by: Field value and apply a color gradient (e.g., dark to light blue).

[anilelmastasi]

Hello @MattiaFratello ,

Is it possible to send the pbix file?

[MattiaFratello]

Hello @anilelmastasi, unfortunately no, I can try to create something similar from scratch