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.


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.


      • dhrupal_shah's avatar
        dhrupal_shah
        Helper I
        1 year ago

        thanks vinay , i get solution as provided by you.