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.


Angith_Nair's avatar
Angith_Nair
Continued Contributor
1 year ago

Hi dhrupal_shah 

 

To apply conditional formatting based on specific ranges for different states in Power BI, you can use a DAX measure that defines the color based on your rules. Then, you can apply conditional formatting based on this measure.

 

Step 1: Create a Measure for Color Formatting
Go to Modeling > New Measure and create a measure to define the color logic:

FormatColor = 
SWITCH(
    TRUE(),
    'Table'[State] = "Central" && 'Table'[Value] >= 1 && 'Table'[Value] <= 10000, "#FF0000", // Red for Central, 1 - 10000
    'Table'[State] = "Central" && 'Table'[Value] > 10000 && 'Table'[Value] <= 15000, "#008000", // Green for Central, 10000 - 15000
    'Table'[State] = "East" && 'Table'[Value] >= 1 && 'Table'[Value] <= 2000, "#FF0000", // Red for East, 1 - 2000
    'Table'[State] = "East" && 'Table'[Value] > 2000 && 'Table'[Value] <= 5000, "#FFFF00", // Yellow for East, 2000 - 5000
    'Table'[State] = "East" && 'Table'[Value] > 5000, "#008000", // Green for East, > 5000
    "#FFFFFF" // Default to white if none of the conditions are met
)

 

Step 2: Apply Conditional Formatting in Power BI
> Select your table or matrix visual.
> Go to the Format your visual pane, expand Conditional formatting, and choose Background color or Font color based on preference.
> Under Format by, select Field value.
> In the Based on field dropdown, select the FormatColor measure.


If new states with different ranges are added, you can update the FormatColor measure by adding additional conditions.

  • 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