Conditional Formatting Matrix Columns

[PRodriguez]

Hello,

I am trying to figure out if there is a way to use conditional formatting to go on the basis of the column, rather than the entirety of the matrix.

For example, when I use the Lowest value to Highest value format, it will compare the values of the whole table, when I’d like the conditional formatting to compare lowest to highest of only the values in columns.

Thank You for your help,

[Siddhesh_P]

To attain this objective, utilize conditional formatting that relies on the proportion of a measure's contribution to the overall total, rather than the raw measure itself. This ensures that the column formatting is determined by values always ranging between 0 to 1, as depicted in the image.

The dax for achieving this is as follows:

Month wise Qty Contribution =

Var _qty = [Qty]

VAR _total_qty =

    CALCULATE(

        [Qty],

        ALL('Calendar'[Month])

    )

RETURN

    DIVIDE(_qty, _total_qty)

[SeanM77]

FYI this can be done in Tableau

[jnickell]

I found this thread looking for the same conditional formatting color scale "per column".

I thought I was close using RankX to get the format key below, but that still didn't work.

I think that I've found an answer in this youtube video.. https://www.youtube.com/watch?v=wTRrskQzAHk

He brings it together at the end, and it looks to work in my scenario.

__PerMonthColorScale = 
    VAR SummaryTable = 
        CALCULATETABLE(
                ADDCOLUMNS(
                    SUMMARIZE(
                        Forecasts,
                        Projection_Master[JOBNUMBER_I],
                        Forecasts[TargetMonth]      
                ),
                "CurrentProjection", [Current Projection]
            ), 
            ALLSELECTED('Job Summary')
        )
    VAR MaxValue = 
        MAXX(
            SummaryTable,
            [CurrentProjection]
        )
    VAR MinValue = 
        MINX(
            SummaryTable,
            [CurrentProjection]
        )
    VAR Range = MaxValue - MinValue
    VAR Hue = 
        Round(
            DIVIDE(
                [Current Projection] - MinValue,
                Range
                ) * 120,0)
    VAR Color = "hsla(" & Hue & ", 100%, 50%, 1)"
    RETURN
        if (ISBLANK([Current Projection]),
            BLANK(),
            color
        )

[JonesJonesJones]

For anyone searching, the below works:

Formatting Measure =
RANKX (
    ALLSELECTED ( Matrix Rows ),
    [Matrix Measure],
    [Matrix Measure],
    ASC
)

I don't understand why the measure is in there twice, or what it's doing. But I know it works.

If you have a very narrow range of values, the highlighting effect won't be very pronounced.

[lgroger]

If anyone else stumbles across this like me, I voted for this idea

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33844120-conditional-formatting-o...

[manuelbanza]

Hello,

Does anyone know if there is already a solution for the question asked?

Thank you for your help,

[Anonymous]

I got around this by using the ALLEXCEPT() function a measure. This function will ignore all filters but one, so if you choose the column value as the exception you can use conditional formatting on a column basis.

[TorbenL]

@Anonymous I'm trying to figure this out as weel. Can you provide an example of how you managed to solve this by ALLEXCEPT?

Best,

Torben

[AndrewJames]

Did anyone solve this issue?

[lgroger]

Can you provide an example of how you did this? 

[v-yuezhe-msft · Accepted solution]

@PRodriguez,

Conditional formatting applies to a range of values in a single column of your original table, when you create a Matrix visual using fields of the original table, all values come from this single column,  thus conditional formatting goes on the entirety of the Matrix.

The feature you describe can’t be achievable in Power BI Desktop, you can submit a feature request in the Power BI ideas forum: https://ideas.powerbi.com/forums/265200 .

Thanks,
Lydia Zhang

[Anonymous]

Hello Lydia,

I have a PBI dashboard where conditional formatting is being applied to a single column of a matrix.  In this case, all dollar values < 0 are formatted as white text on red background.  I inherited a dashboard and I am struggling to figure out how this is done.  I'm totally lost as to how this is implemeted.

[Shrap09]

This is ridiculous, please add this feature.

[PRodriguez]

Hello,

I am trying to figure out if there is a way to use conditional formatting to go on the basis of the column, rather than the entirety of the matrix.

For example, when I use the Lowest value to Highest value format, it will compare the values of the whole table, when I’d like the conditional formatting to compare lowest to highest of only the values in columns.

Thank You for your help,