Forum Discussion

mogugu_84's avatar
mogugu_84
Helper I
1 year ago

conditionally formatting by column

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? 

 

 

 

Need Help

9 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    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's avatar
      mogugu_84
      Helper I
      1 year ago

      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's avatar
        shafiz_p
        Super User
        1 year ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      mogugu_84
      Helper I
      1 year ago
      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"
          )
      )
       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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