- 9 minutes to read
APPLIES TO: PowerBDesktopPowerBI service
With conditional formatting for tables and matrices in Power BI, you can specify custom cell colors, including color gradients, based on field values. You can also represent cell values with data bars or KPI icons, or as live web links. You can apply conditional formatting to any text or data field, as long as the formatting is based on a field that has numeric, color name or hexadecimal code, or web URL values.
To apply conditional formatting, select alow hilloSitepreview in Power BI Desktop or the Power BI service. In itPoints of viewpanel, right-click or select the down arrow next to the field in thevaluesso you want to format . selectconditional formatand select the type of format to apply.
Conditional formatting replaces any custom background or font color applied to the conditionally formatted cell.
To remove conditional formatting from a visualization, selectremove conditional formattingin the dropdown menu for the field and select the type of formatting you want to remove.
The following sections describe each conditional formatting option. You can combine more than one option in a single table column.
Background format or font color
To format the cell background or font color, selectconditional formatfor a field and then selectBackground coloroFont Colorno suspense menu
oBackground coloroFont ColorThe dialog box opens, with the name of the field you are formatting in the title. After selecting the conditional formatting options, selectOK.
oBackground colormiFont Colorthe options are the same but affect the cell background color and font color respectively. You can apply the same or a different conditional formatting to the font color and background color of a field. If you set the same color for the font and background of a field, the font will be blended with the background so that the table column only shows the colors.
Color by color scale
To format the cell background or font color by color scale, in theformat stylefield ofBackground coloroFont Colordialog, selectGradient. UnderOn what basis should we base this?, select the field on which to base the format. You can base the format on the current field or on any field in your model that has numeric or colored data.
Underresume, specify the type of aggregation you want to use for the selected field. Understandard format, select a format to apply to blank values.
UnderMinimummiMaximum, choose whether to apply the color scheme based on the highest and lowest field values or custom entered values. Scroll down and select the color swatches you want to apply to the minimum and maximum values. Select theAdd an intermediate colorcheck box to also specify acentervalue and color.
An example table formatted with color scale background inAccessibilitycolumn looks like this:
The example table with color scale font format inAccessibilitycolumn looks like this:
color by rules
To format cell background or font color by rules, informat stylefield ofBackground coloroFont Colordialog, selectStandards. Again,On what basis should we base this?displays the field on which to base the format andresumedisplays the aggregation type for the field.
UnderStandards, enter one or more ranges of values, and define a color for each. Each range of values has aI valuecondition, amivalue condition and a color. Cell backgrounds or fonts in each value range are colored with the given color. The following example has three rules:
when you selectPercentIn this dropdown menu, you are defining the limits of the rule as a percentage of the overall range of values from minimum to maximum. So, for example, if the lowest data point is 100 and the highest is 400, the above rules would color any point less than 200 as green, any point between 200 and 300 as yellow, and any point above 300 as red. .
An example table with rule-based background color formatting based onPercentnoAccessibilitycolumn looks like this:
Color by rules for percentages
If the field on which the format is based contains percentages, write the numbers in the rulers as decimals, which are the actual values; for example, ".25" instead of "25". Also, selectNumberinstead ofPercentfor the number format. For example, "If value is greater than or equal to 0 Number and is less than 0.25 Number" returns values less than 25%.
In this example table with rule-based background color in% income regioncolumn, 0 to 25% is red, 26% to 41% is yellow, and 42% and up is blue:
and usePercentinstead ofNumberfor fields that contain percentages, you may get unexpected results. In the example above, in a range of percentage values from 21.73% to 44.36%, 50% of that range is 33%. then useNumberinstead of.
Color by color values
If you have a named field or measure with color or hex value data, you can use conditional formatting to automatically apply those colors to a column background or font color. You can also use custom logic to apply colors to the font or background.
The field can use any color value listed in the CSS color specification athttps://www.w3.org/TR/css-color-3/. These color values can include:
- 3-, 6-, or 8-digit hexadecimal codes, for example #3E4AFF. Be sure to include the # symbol at the beginning of the code.
- RGB values from RGBA, such as RGBA(234, 234, 234, 0.5).
- HSL or HSLA values, such as HSLA (123, 75%, 75%, 0.5).
- Color names, such as Green, SkyBlue, or PeachPuff.
The following table has a color name associated with each state:
to format theheartcolumn based on your field values, selectconditional formatfor himheartfield and then selectBackground coloroFont Color.
NoBackground coloroFont Colordialog, selectfield valueofformat stylesuspended field.
An example table based on the value of the color fieldBackground colorformatting inheartfield looks like this:
if you also usefield valueto format the columnFont Color, the result is a solid color on theheartcolumn:
Color based on a calculation
You can create a calculation that generates different values based on selected business logic conditions. Generally, creating one formula is faster than creating multiple rules in the conditional formatting dialog.
For example, the following formula applies hexadecimal color values to a newaccessibility ratingcolumn, based on existingAccessibilitycolumn values:
To apply the colors, selectBackground coloroFont Colorconditional format forAccessibilitycolumn and base the format on thefield valuedoaccessibility ratingcolumn.
The example table withAccessibilitybackground color based on calculatedaccessibility ratingIt looks like this:
You can create many more variations, just using your imagination and some calculations.
add data bars
To display data bars based on cell values, selectconditional formatfor himAccessibilityfield and then selectdata barsno suspense menu
Nodata barsdialogue, theshow bar onlyThe option is unchecked by default, so the table cells display bars and actual values. To display only the data bars, select theshow bar onlyselection box
you can specifyMinimummiMaximumdata bar values, colors and direction, and axis color.
With data bars applied to theAccessibilitycolumn, the example table looks like this:
To display icons based on cell values, selectconditional formatfor the field and then selecticonsno suspense menu
Noiconsdialogue bassformat style, select anyStandardsofield value.
To format by rules, select oneOn what basis should we base this?,resumemethod,icon design,icon alignmenticonStyle, and one or moreStandards. UnderStandards, enter one or more rules with aI valuecondition and amivalue condition and select an icon to apply to each rule.
To format by field values, select oneOn what basis should we base this?,resumemethod,icon design, miicon alignment.
The following example adds icons based on three rules:
selectOK. With icons applied to theAccessibilitycolumn by rules, the example table looks like this:
Format as web URL
If you have a column or measure that contains website URLs, you can use conditional formatting to apply those URLs to the fields as direct links. For example, the following table has awebsitecolumn with website URLs for each state:
To display the name of each state as an active link to your website, selectconditional formatfor himExpressfield and then selectURL web. NoURL webdialog box, inOn what basis should we base this?, selectwebsiteand then selectOK.
ComURL webformat applied toExpressfield, each state name is an active link to your website. The following example table hasURL webformat applied toExpresscolumn and conditionaldata barsapplied toGeneral ratingcolumn.
Totals and subtotals
You can apply conditional formatting rules to totals and subtotals, for both table and matrix visuals.
Conditional formatting rules are applied using therequestdropdown menu in conditional formatting, as shown in the following image.
You must manually define thresholds and ranges for conditional formatting rules. For matrices,valueswill refer to the lowest visible level of the array hierarchy.
Text-based color coding
Unlike Excel, you cannot color-code text values to display as a specific color, such as "Accepted" = blue, "Rejected" = red, "None" = gray. You create measures related to these values and apply formatting based on them.
For example, StatusColor = SWITCH('Table'[Status], "Accepted", "Blue", "Rejected", "Red", "None", "Gray")
prontoBackground colordialog box, format thestatefield based on the values in theStateCorcampo.
In the resulting table, the format is based on the value of theStateCorfield, which in turn is based on the text of thestatecampo.
Considerations and limitations
There are a few considerations to keep in mind when working with conditional table formatting:
- Any table that does not have a grouping is displayed as a single row that does not support conditional formatting.
- You cannot apply gradient formatting with automatic max/min values or rule-based formatting with percentage rules if your data containsYayavalues. NaN stands for "Not a number", most commonly caused by a division by zero error. you can use theDAX DIVIDE() functionTo avoid these mistakes.
- Conditional formatting requires an aggregation or measure to apply to the value. That's why you see 'First' or 'Last' incolor by valueexample. If you are designing your report on an Analysis Service multidimensional cube, you cannot use an attribute for conditional formatting unless the cube owner has created a measure that provides the value.
For more information on the color format, seeTips and tricks for color format in Power BI