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.


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.

    • dhrupal_shah's avatar
      dhrupal_shah
      Helper I
      1 year ago

      Dear vinay,

       

      same condition with date not work.

       

      Production_Color =
      SWITCH(
      TRUE(),
      TRIM('Production'[ComponentName]) = "CTL 1" && 'Production'[QtyRecv] > 50 , "#008000", // Green
      TRIM('Production'[ComponentName]) = "CTL 1" && 'Production'[QtyRecv] >=35 && 'Production'[QtyRecv] <= 50, "#000a00", // Black
      TRIM('Production'[ComponentName]) = "CTL 1" && 'Production'[QtyRecv] > 0 && 'Production'[QtyRecv] < 35, "#FF0000" //Red
      )

       

       

      thanks in advance.

      Dhrupal

      this data from sql query.