Ravindra BagaleCourses & study guides

20. Dynamic Text and Dynamic Colour (Conditional Formatting)

20.6 Conditional Formatting: Rules, Gradient and Field Value

By Last updated:

Conditional formatting changes colours or icons based on values. There are three format styles:

Format style How it works Example
Gradient (colour scale) Lowest value → one colour, highest → another (optional middle colour) Avg Delivery Time: green (low) → yellow → red (high)
Rules If value is in a range, then use a colour ≤ 10 green, > 10 and ≤ 15 amber, > 15 red
Field value A column or measure returns the colour (name or hex code like #D13438) A DAX colour measure (reusable everywhere)

The DAX colour measure (field value):

Delivery Time Colour =
VAR t = [Avg Delivery Time (mins)]
RETURN
SWITCH(TRUE(),
    ISBLANK(t), BLANK(),
    t <= 10, "#107C10",     -- green
    t <= 15, "#F2A900",     -- amber
    "#D13438")              -- red: slower than 15 minutes

Steps in Power BI – colour the Avg Delivery Time column in a table

  1. Build a table: Store Name, Total Orders, Avg Delivery Time (mins), Cancellation Rate.
  2. In Build visual, click the drop-down arrow on Avg Delivery Time (mins) in Columns › Conditional formatting › Background color. (Or Format visual › Visual › Cell elements › Apply settings to the column › Background color On › fx.)
  3. Format style: Rules. Apply to: Values only. What field should we base this on?: Avg Delivery Time (mins). Summarization: (measure).
  4. Rules: If value ≥ 0 Number and ≤ 10 Number then green; > 10 and ≤ 15 amber; > 15 and ≤ 999 red. Click + New rule for each › OK.
  5. Or choose Format style Field value › [Delivery Time Colour] › OK. The same colours are now controlled by one measure.
  6. Font color: use white font for the red cells with another field-value measure.

Empty values

In the Gradient dialog, How should we format empty values? lets you choose how blanks are shown (for example Don't format or a specific colour). Cancelled-only stores with no delivery time will not look "perfect green".

Conditional formatting ne report "bolto". Pan rang jast vapru naka – fakt mahatvache highlight kara.

Ravindra Bagale's Tip

Friends, many students use a colour gradient on a measure where low is good (delivery time) with green at the high end. Check the direction of every colour rule, or drive the colour from a measure that returns a colour name or hex code based on business thresholds. This matters for both exams and interviews.