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.


Kedar_Pande's avatar
Kedar_Pande
Super User
1 year ago

dhrupal_shah 

Create a Measure for Conditional Formatting

ConditionalColor = 
SWITCH(
TRUE(),
SELECTEDVALUE('YourTable'[Region]) = "Central" && 'YourTable'[Value] >= 1 && 'YourTable'[Value] <= 10000, "Red",
SELECTEDVALUE('YourTable'[Region]) = "Central" && 'YourTable'[Value] > 10000 && 'YourTable'[Value] <= 15000, "Green",
SELECTEDVALUE('YourTable'[Region]) = "East" && 'YourTable'[Value] >= 1 && 'YourTable'[Value] <= 2000, "Red",
SELECTEDVALUE('YourTable'[Region]) = "East" && 'YourTable'[Value] > 2000 && 'YourTable'[Value] <= 5000, "Yellow",
SELECTEDVALUE('YourTable'[Region]) = "East" && 'YourTable'[Value] > 5000, "Green",
"No Color" // Fallback
)

Select the Visual, Go to Format Pane, Click on the fx button next to Conditional formatting, Choose your ConditionalColor measure in the Based on field dropdown

 

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

  • dhrupal_shah's avatar
    dhrupal_shah
    Helper I
    1 year ago

    Thanks for your reply , i have below condition 

    Product   South  East   West  North

    Prod 1       5000   200  9000  11000

    Prod 2       2000   900  3000    1000

    Prod 3       1000       0    500  15000