Skip to content
Tutorials Center
My learning

Topics

Lesson 40 of 56 ยท Visuals and Reports

Conditional Formatting, KPIs and Cards

Use rules, gradients, icons and field-value formatting, the KPI visual, reference labels and small multiples to make Power BI numbers explain themselves.

A table full of numbers makes readers do the work: is 87,400 good or bad? Conditional formatting moves that judgement into the report. Cells, bars, data labels and titles change colour or show an icon depending on the value, so the eye lands on what matters. Add cards, the KPI visual and small multiples, and a page can answer "how are we doing?" in a few seconds.

This lesson covers the formatting options (rules, gradients, field value, icons), the difference between a card and a KPI, reference labels and small multiples. The features live in the Power BI Desktop interface, so the steps are described in text and the DAX is read-only. The runnable examples are Python stand-ins (pandas in your browser) that apply the same logic to a small table, so you can see exactly what a rule does to each value.

Where conditional formatting lives

Conditional formatting is available for many things, not only table cells. In the Format pane, look for the small fx button next to a setting. Clicking it opens a dialog where the setting stops being a fixed value and becomes a rule.

Typical targets:

  • Table and matrix: cell background colour, font colour, data bars, icons and web URL.
  • Bar and column charts: the colour of the bars, so a bar can turn red below target.
  • Data labels, titles and subtitles: a title colour or even title text driven by a measure.
  • Shapes, buttons and lines: a coloured status dot made from a shape.

Four ways to format by value

Gradient Colour scales from low to high.
Rules If value is above X, use this colour.
Field value A measure returns the colour itself.
Icons Arrows and dots beside the number.

The first three set a colour; icons add a symbol.

1 / 2

Gradient: a colour scale

A gradient (Format style: Gradient) colours each value between a minimum and a maximum colour. You choose a field to base it on, usually the same column, the colour for the lowest and highest values and optionally a middle colour. Power BI computes the minimum and maximum from the data, or you can fix them to numbers such as 0 and 100. This is the colouring you see in heat-map style matrices.

Gradients suit values on a continuous scale where the reader should compare shades: sales by region and month, response times, scores. Keep the palette simple (light to dark of one colour is easiest to read) and avoid red-to-green scales alone, which many readers with colour-vision deficiency cannot tell apart.

Rules: thresholds you define

With Rules, you list conditions in order, for example "if value is greater than or equal to 100 percent, green; if it is greater than or equal to 80 percent, amber; otherwise red". Each rule has a comparison, a number (or a percentage of the range) and a colour. Power BI checks the rules from the top down and uses the first one that matches.

Rules are the right tool when the thresholds are business decisions: a target, a service-level limit, a budget. They are fixed in the report, though. If the thresholds change often or differ by product, rules become a maintenance burden, and a measure is better.

Field value: let a measure choose the colour

The most flexible style is Field value. You point the formatting at a field that returns a colour, usually a measure that outputs a hex code such as "#2E7D32". The measure can use any logic DAX supports, including comparing to a target stored in the model:

DAX
Status Colour =
VAR Actual = [Total Sales]
VAR Target = [Sales Target]
RETURN
    SWITCH(
        TRUE(),
        ISBLANK(Target), "#9E9E9E",
        Actual >= Target, "#2E7D32",
        Actual >= Target * 0.9, "#F9A825",
        "#C62828"
    )

Because the logic lives in one measure, you can reuse it on a table, a bar chart and a card title, and change the thresholds in one place. If you do not know SWITCH or variables yet, see CALCULATE and Filter Context and Common DAX Patterns.

Remember that a colour is a text value. If the measure returns something that is not a valid colour, Power BI shows no formatting instead of an error, which makes mistakes quiet. Test with a plain table first.

Do not rely on colour alone. About one in twelve men has some colour-vision deficiency, and printed reports lose colour. Pair colour with an icon, a sign or a label, as the next section shows.

Icons

The Icons style puts a symbol (arrow, traffic light, flag, check mark) next to the value, chosen by rules or by a field. You can place the icon to the left or right of the number, or show the icon alone. Icons are especially useful in tables where a column of coloured cells would be hard to read in grayscale. You can base the icon on a field value too, with a measure returning an icon name or an image URL, but the built-in sets cover most needs.

Stand-in: rules and gradients

Power BI's rule dialog is a list of conditions checked from the top down. The first example reproduces that list in Python and prints a status for each region. The second computes a gradient: each value mapped to a position between 0 and 1 between the minimum and maximum, which is what the colour scale does before choosing a colour.

Python Stand-in: rule-based status

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Python Stand-in: a gradient colour scale

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The loop in the first example is the logic of a Rules dialog; swapping the list of tuples for a measure that returns a colour is the Field value approach. The second example shows why a single extreme value flattens a gradient: the minimum and maximum come from the data, so one outlier compresses everyone else into a narrow band of shades. Setting a fixed minimum and maximum in the dialog is the fix.

Cards: one number

A card shows one value with a label. It is the simplest visual and often the most effective: total sales, number of open tickets, average delivery time. Tips:

  • Use a measure, not a column, so the number respects slicers and filters.
  • Format the display units (thousands, millions) and decimals in the Format pane, and set a clear category label.
  • The newer card visual can show several values in one visual, each with a label and a reference label. The older multi-row card lists several fields one under another.
  • Use conditional formatting on the card's value or title to show good or bad at a glance. The fx option works for the data label colour just like in tables.

A card's value changes with the filter context, which makes it a good first target for the check "is this the same number as the table?". Mismatching a card and a total row is a common sign of a measure or relationship issue.

The KPI visual

The KPI visual shows a value, a target and an optional trend line in one tile. You supply three fields:

  • Value: the measure to evaluate, such as [Total Sales].
  • Trend axis: usually a date field, which draws a small area chart behind the number.
  • Target: a measure for the goal, such as [Sales Target].

The visual compares the latest value on the trend axis to the target and colours it good or bad. By default, "higher is better"; in the Format pane you can reverse it for measures like cost or defects, where lower is better. It shows the difference between actual and target as a number or a percentage.

Two details catch people. First, the KPI visual uses the last value on the trend axis as the current value, so ordering and incomplete months matter: if the current month has only a few days of data, the KPI will look weak. Second, the target is a measure, so it should be defined in the model, for example as a measure over a Targets table or as a fixed Sales Target = 100000 while you prototype.

When you need more layout control than the KPI visual gives, build your own KPI: a card for the value, another card for the target, a shape or card showing the variance and a status colour from a field-value measure.

Reference labels and small multiples

A card can show a reference label: a second, smaller value placed under or beside the main number, for example "vs last year: +4.2%". It is a measure like any other, so you can colour it with a field value too. Some visuals also show a constant line or average line (Analytics pane) to compare bars with a target.

Small multiples repeat the same chart for each value of a category, in a grid, sharing axes. Drop a field into the Small multiples well of a column, line, area or bar chart, and Power BI draws a panel for every region, product category or similar. This lets readers compare shapes (trends over time per region, for example) without a cluttered, multi-line chart. Keep the number of panels manageable (around a dozen), and consider fixing the Y axis so scales are comparable.

Stand-in: status, variance and panels

Here is the arithmetic a KPI and a small multiples chart do. The first example computes actual versus target and the variance. The second splits the same data by region, as small multiples do, and prints one sparkline-like panel for each.

Python Stand-in: KPI value, target and variance

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Python Stand-in: small multiples

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Each panel in the second example is scaled to the same minimum and maximum, which is the "shared axis" setting. If every panel used its own range, a small region would look as dramatic as a large one.

Design advice

Conditional formatting is easy to overdo. A page where every cell is a different colour has no message.

  • Format the exception, not the rule. Highlight what is off target; leave the rest neutral.
  • One scheme per report. If green means good on one page, it should mean good everywhere, and never mean "high" in one place and "good" in another.
  • Keep thresholds in the model (a Targets table or measures) instead of scattered across fx dialogs, so a target change is made once.
  • Check in grayscale or with a colour-blindness simulator, and add icons or text for status.
  • Label the target. A red number with no visible target leaves the reader guessing.

The next lessons put the report on the web: publishing, sharing and securing it.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You want the colour of a bar to follow logic that compares Actual to a Target stored in the model. Which formatting style fits best?

    Field value lets a DAX measure return the colour, so the logic can use variables, targets and any condition. A gradient colours by position on a scale, and a fixed colour cannot change.
  2. 2. How does Power BI evaluate the list of rules in a Rules-based conditional format?

    Rules are checked in order and the first match decides the colour, so put the most specific condition first and leave the 'otherwise' colour as the fallback.
  3. 3. Which fields does the KPI visual use?

    The KPI visual compares the value (latest point on the trend axis) with the target, and draws the trend behind the number.
  4. 4. Why should status not be shown with colour alone?

    Colour-vision deficiency and black-and-white printing make colour-only signals unreliable. Add icons, signs or text to carry the meaning as well.
  5. 5. What do small multiples do?

    Small multiples draw one panel per category value with shared axes, which makes shapes easy to compare without cluttering a single chart.

Summary

  • The fx button turns a fixed format setting into a gradient, rules list, field value (a measure returning a colour) or icons.
  • Rules are checked top-down and the first match wins; gradients depend on the data's minimum and maximum unless you fix them.
  • A card shows one measure; the KPI visual shows value, trend axis and target, using the latest point as the current value.
  • Reference labels add context under a number, and small multiples repeat a chart per category with shared axes.
  • Format exceptions, keep thresholds in the model, be consistent and never rely on colour alone.
  • Everything above is a feature of Power BI Desktop; the Python examples are stand-ins for the logic, not the real formatting engine.

Next, take your finished report online with Publishing to the Power BI Service.