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

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

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.