conditionally formatting by column

[mogugu_84]

Hi 

i hope to create a conditionally formatting based on individual column of the matrix table below (preferrably gradient color scale), i.e. 3M within product P, not 3M across both Product P and Product LA...anyone knows how to write a formula for that? 

[v-heq-msft]

Hi @mogugu_84 ,

As he said, for a customized format for a specific Value in the matrix, you can first determine under which column classification it is located, in the data you provided for Molecule.You can view the following pbix file. If it still doesn't solve your problem, you can provide the full example data and MEASURES, preferably the example pbix file, so we can help you faster.Please hide sensitive information in advance.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[mogugu_84]

Thank you, the example file you provide is helpful, but i still couldn't get it right...

Tried to replicate from your "Color" measure to mine as below, did i get it right? the aim is to compare to the total value of "3M G%" under Product P, i.e. if it's greater than 15.3%, color green, if below, orange, if negative, red

Color =

IF(

    SELECTEDVALUE('Def market'[Molecule]) <> "Product P",

    "No color",

    IF(

        [3M Growth] < 0.15 ,

        "Red",

        "Green"

    )

)

[v-heq-msft]

Hi @mogugu_84 ,
You can try this code

Color =
IF(
    SELECTEDVALUE('Def market'[Molecule]) <> "Product P",
    "No color",
    IF(
        [3M G%] < 0 ,
        "Red",
        IF(
           [3M G%] <= 0.153,
           "Orange",
           "Green"
        )
    )
)

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[mogugu_84]

thank you, anyway i can make this number dynamic, instead of put it as 15.3%?

[v-heq-msft]

Hi @mogugu_84 ,
Sure. You can replace it with measure which you want to compare.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[mogugu_84]

I think i need to replace this yellow bit with a new measure that calculates 3M G% ignoring the row label...which is "Account" in this case....can you help with this formula? Thanks again!

[shafiz_p]

Hi @mogugu_84  

To achieve this you need to create a measure for backgroud color of 3M for column P only not the L.

Try code pattern below for your case:

If single color:

RevenueColor = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Calendar'[Quarter]) IN {1, 2}, "#FFCCCB",  // Light Red for Q1 and Q2
    BLANK()  // No color for other quarters
)

If gradient color scale:

RevenueGradientColor = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Calendar'[Quarter]) IN {1, 2}, 
    VAR RevenueValue = [Revenue]
    RETURN 
        IF(
            RevenueValue <= 100000, "#FFCCCB",  
            IF(
                RevenueValue <= 500000, "#FF9999",  
                "#FF6666"
            )
        ),
    BLANK()  // No color for other quarters
)

Go to cell element > select measure (In your case 3M) > turn on background color > click fx > select field value > and select newly created measure. See image:

Output:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Bes Regards,
Shahariar Hafiz

[mogugu_84]

Hi Shafiz

thank you! however, sorry i didn't explain the measure of "3M" properly, it is a calculated value for rolling 3 months vs LY, i.e. Oct 24+Sep 24+Aug 24 vs Oct 23+Sep 23+Aug 23....so the formulas containing 'calendar[quarter]' would not work.....plus i also need to replicate this conditional format process for the value of "Fiscal year to date vs LY, and rolling 12 months vs LY too......

[shafiz_p]

The formula I have provided is just an example, how could you color background of specific measure of a specific column. It does not matter how your measure is evaluating. It is a matter which column and which measure. You can compare measure with other measure or fixed value for gradient color scale. As I understood from your post. Also, calendar[quarter] is my column name to demonastrate the scenario. Your column name is different (from the picture, i can see it is molecule). You need to replace column name and measure name accordingly.

Hope you got the point.