Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PRodriguez
New Member

Conditional Formatting Matrix Columns

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,

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

@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.
1.PNG



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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

14 REPLIES 14
Siddhesh_P
Regular Visitor

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.

Siddhesh_P_1-1708454947939.png

 

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
Advocate I
Advocate I

FYI this can be done in Tableau

jnickell
Helper V
Helper V

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.

2022-05-04 10_23_06-Book3 - Excel.png

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
New Member

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
Advocate I
Advocate I

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
Frequent Visitor

Hello,

 

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

 

Thank you for your help,

Anonymous
Not applicable

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.

@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

Did anyone solve this issue?

Can you provide an example of how you did this? 

v-yuezhe-msft
Employee
Employee

@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.
1.PNG



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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

This is ridiculous, please add this feature.

PRodriguez
New Member

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,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.