Forum Discussion

dhrupal_shah's avatar
dhrupal_shah
Helper I
1 year ago
Solved

conditional formatting

I want to apply conditional formatting based of column value with range. e.g. in Central if value between 1 to 10000 then red and 10000 to 15000 then green, in East value between 1 to 2000 then red a...
Need Help
  • Vinay_eshwara's avatar
    Vinay_eshwara
    1 year ago

    I can see that your data is in long format and you have three columns Product, Zone and Amount. And you used Matrix visual. 
    Now you can directly create a new calculated column because calculated columns evaluate row by row . In your case, the calculated column can evaluate the Zone and Amount values for each row directly.
    You can use the below DAX for new column

    ZoneColor =
    SWITCH(
        TRUE(),
        'Table'[Zone] = "Central" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 4000, "#FF0000", // Red for Central, 1 - 4000
        'Table'[Zone] = "Central" && 'Table'[Amount] > 4000 && 'Table'[Amount] <= 10000, "#008000", // Green for Central, 4000 - 10000
       
        'Table'[Zone] = "East" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 500, "#FF0000", // Red for East, 1 - 500
        'Table'[Zone] = "East" && 'Table'[Amount] > 500, "#008000", // Green for East, > 500

        'Table'[Zone] = "North" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 10000, "#FF0000", // Red for North, 1 - 10000
        'Table'[Zone] = "North" && 'Table'[Amount] > 10000, "#008000", // Green for North, > 10000

        'Table'[Zone] = "South" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 5000, "#FF0000", // Red for South, 1 - 5000
        'Table'[Zone] = "South" && 'Table'[Amount] > 5000, "#008000", // Green for South, > 5000

        'Table'[Zone] = "West" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 20000, "#FF0000", // Red for West, 1 - 20000
        'Table'[Zone] = "West" && 'Table'[Amount] > 20000, "#008000", // Green for West, > 20000

        "#FFFFFF" // Default color (white) if no condition is met
    )

    To create a new column click on three dots of the table in the Data pane. You can customize colors as you wish in the DAX query.
    Then apply conditional formatting for Amount under the Values dropdown in the visualizations pane. 
    Choose Field value and newly created column in conditional formatting as shown below.


    Then the result looks like this

    Please mark this as solution and give kudos if it met your goal.
    Thanks,
    Vinay.


Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi, dhrupal_shah 

Based on your information, I create a sample table:

 

Then create a calculated column, try the following dax:

ColorCategory = 
SWITCH(
    TRUE(),
    [Region] = "Central" && [Value] >= 1 && [Value] <= 10000, "Red",
    [Region] = "Central" && [Value] > 10000 && [Value] <= 15000, "Green",
    [Region] = "Central" && [Value] > 15000, "Blue",
    [Region] = "East" && [Value] >= 1 && [Value] <= 2000, "Red",
    [Region] = "East" && [Value] > 2000 && [Value] <= 5000, "Yellow",
    [Region] = "East" && [Value] > 5000, "Green",
    "No Color"
)

 

Create a table visual and put Region and Value in it. Click Format pane and expend cell elements, you can choose Background color or Font color.

Select Field value, and then select the calculated column you just created

Here is my preview:

 

You can learn more from the following link:

Apply conditional table formatting in Power BI - Power BI | Microsoft Learn

 

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

dhrupal_shah's avatar
dhrupal_shah
Helper I
1 year ago

Thanks for your replay but i have below condition.

 

Product Central   East    West  South     North

Prod1       1000    200    5000    3000     10000

Prod2       2000    500    3000    5000       8000

  • Vinay_eshwara's avatar
    Vinay_eshwara
    Frequent Visitor
    1 year ago

    Hi, dhrupal_shah
    To achieve conditional formatting for your table you need to create separate measures for each color such as,

    Central Color =
    SWITCH(
        TRUE(),
        'Table (2)'[Central] >= 1 && 'Table (2)'[Central] <= 10000, "#FF0000", // Red for Central, 1 - 10000
        'Table (2)'[Central] > 10000 && 'Table (2)'[Central] <= 15000, "#008000", // Green for Central, 10000 - 15000
        "#FFFFFF" // Default to white if none of the conditions are met
    )
    East Color =
    SWITCH(
        TRUE(),
        'Table (2)'[East] >= 1 && 'Table (2)'[East] <= 2000, "#FF0000", // Red for East, 1 - 2000
        "#FFFFFF" // Default to white if none of the conditions are met
    )
    West Color =
    SWITCH(
        TRUE(),
        'Table (2)'[West] >= 1 && 'Table (2)'[West] <= 3000, "#FF0000", // Red for West, 1 - 3000
        'Table (2)'[West] > 3000 && 'Table (2)'[West] <= 5000, "#008000", // Green for West, 3000 - 5000
        "#FFFFFF" // Default to white if none of the conditions are met
    )
    North Color =
    SWITCH(
        TRUE(),
        'Table (2)'[North] >= 1 && 'Table (2)'[North] <= 3000, "#FF0000", // Red for North, 1 - 3000
        'Table (2)'[North] > 3000 && 'Table (2)'[North] <= 10000, "#008000", // Green for North, 3000 - 5000
        "#FFFFFF" // Default to white if none of the conditions are met
    )
    After creating separate measures you can apply conditional formatting for each column by selecting Background color in cond formatting and  secting field value and selecting respective color measure.
    Here is a screenshot for reference.

     After applying conditional formatting for each column the result looks like below image

    If any new columns or new rows are added just update the measures and it works.

    If you find my solution helpful please give a kudos.
    Thanks,
    Vinay.

     

    Preview
     
     
     
    • dhrupal_shah's avatar
      dhrupal_shah
      Helper I
      1 year ago

        

      Thanks vinay, your reply near to my solution , please help me to solve it. please refer my 

      below data and screen shots.

      ProductZoneAmount
      Prod1Central5000
      Prod2Central2000
      Prod3Central6000
      Prod4Central5000
      Prod5Central4000
      Prod1East500
      Prod2East200
      Prod3East1000
      Prod4East300
      Prod5East200
      Prod1North15000
      Prod2North10000
      Prod3North4000
      Prod4North20000
      Prod5North5000
      Prod1South8000
      Prod2South9000
      Prod3South7000
      Prod4South8000
      Prod5South8000
      Prod1West25000
      Prod2West10000
      Prod3West25000
      Prod4West20000
      Prod5West20000

       

       

       

      • Vinay_eshwara's avatar
        Vinay_eshwara
        Frequent Visitor
        1 year ago

        I can see that your data is in long format and you have three columns Product, Zone and Amount. And you used Matrix visual. 
        Now you can directly create a new calculated column because calculated columns evaluate row by row . In your case, the calculated column can evaluate the Zone and Amount values for each row directly.
        You can use the below DAX for new column

        ZoneColor =
        SWITCH(
            TRUE(),
            'Table'[Zone] = "Central" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 4000, "#FF0000", // Red for Central, 1 - 4000
            'Table'[Zone] = "Central" && 'Table'[Amount] > 4000 && 'Table'[Amount] <= 10000, "#008000", // Green for Central, 4000 - 10000
           
            'Table'[Zone] = "East" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 500, "#FF0000", // Red for East, 1 - 500
            'Table'[Zone] = "East" && 'Table'[Amount] > 500, "#008000", // Green for East, > 500

            'Table'[Zone] = "North" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 10000, "#FF0000", // Red for North, 1 - 10000
            'Table'[Zone] = "North" && 'Table'[Amount] > 10000, "#008000", // Green for North, > 10000

            'Table'[Zone] = "South" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 5000, "#FF0000", // Red for South, 1 - 5000
            'Table'[Zone] = "South" && 'Table'[Amount] > 5000, "#008000", // Green for South, > 5000

            'Table'[Zone] = "West" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 20000, "#FF0000", // Red for West, 1 - 20000
            'Table'[Zone] = "West" && 'Table'[Amount] > 20000, "#008000", // Green for West, > 20000

            "#FFFFFF" // Default color (white) if no condition is met
        )

        To create a new column click on three dots of the table in the Data pane. You can customize colors as you wish in the DAX query.
        Then apply conditional formatting for Amount under the Values dropdown in the visualizations pane. 
        Choose Field value and newly created column in conditional formatting as shown below.


        Then the result looks like this

        Please mark this as solution and give kudos if it met your goal.
        Thanks,
        Vinay.