Conditional Formatting in Power BI
Conditional formatting changes colour, icons, or data bars from the value itself — so a late delivery turns red and a strong city stays green without the reader scanning every cell.
Friends! A table full of black numbers hides risk. Why? The eye needs a stop light. How? We format a fictional FreshBasket delivery column with a simple rule (≥ 30 minutes = red), then we peek at gradient and a colour measure. Rules first. Calm colours.
मित्रांनो! काळ्या numbers ची table risk लपवते. का? डोळ्यांना सिग्नल लाईट हवी. कसे? FreshBasket च्या delivery कॉलमला सोपा नियम (≥ 30 मिनिटे = लाल), मग gradient आणि colour measure. आधी Rules. शांत रंग.
मित्रों! काले numbers वाली table risk छुपाती है. क्यों? आँखों को सिग्नल लाइट चाहिए. कैसे? FreshBasket के delivery कॉलम पर आसान नियम (≥ 30 मिनट = लाल), फिर gradient और colour measure. पहले Rules. शांत रंग.
Quick answer
Vertical path:
- Select a table or matrix with
[Delivery Mins]or[Total Sales]. - Format visual → Cell elements (or the colour fx on a chart).
- Background colour → Rules → ≥ 30 → red.
- Use Gradient when you want a heat map, not a hard cut.
- Use Field value when a DAX colour measure returns
#E11D48. - Read the page: red must mean the business sentence you wrote.
Real example: phone-bill days late, shop deliveries
Shop delivery sample (fictional):
| City | Order | Delivery Mins |
|---|---|---|
| Mumbai | M-1 | 18 |
| Mumbai | M-2 | 35 |
| Pune | P-1 | 12 |
Owner rule: 30 minutes or more is late.
What conditional formatting does:
- Rows stay the same three rows.
- M-2’s cell turns red. M-1 and P-1 stay calm.
- The number 35 does not change. Only the paint changes.
- A card of average minutes can use the same idea with a colour measure later.
Phone-bill cousin: months where the bill jumped above 500 can turn amber. Same tool, different column.
What do I need before this guide?
- A table or matrix (matrix guide).
- A measure you trust (CALCULATE if the KPI is filtered).
- Course: Conditional formatting.
Before and after (look at the tables first)
Before
आधी (Before)
पहले (Before)
After
नंतर (After)
बाद में (After)
Three format styles
Conditional formatting paints cells or bars from rules, a colour scale, or a colour measure (field value).
Conditional formatting rules, colour scale, किंवा colour measure (field value) वरून सेल किंवा बार रंगते.
Conditional formatting rules, colour scale, या colour measure (field value) से सेल या बार रंगता है.
- Rules — hard bands. Above 30 = red. Best for SLA and targets.
- Gradient (colour scale) — low to high blend. Best for heat maps.
- Field value — a measure or column already holds the colour (or a category you map).
Look for the small fx button on colour and title settings — that is how you drive format from a measure.
रंग आणि title सेटिंग्जवर छोटा fx बटण शोधा — measure वरून format चालवण्याचा तो मार्ग आहे.
रंग और title सेटिंग्स पर छोटा fx बटन ढूँढें — measure से format चलाने का वही रास्ता है.
Why the fx button exists: almost every colour and many titles can be driven by a field instead of a fixed pick. Look for fx before you say “Power BI cannot do that”.
KPI colour on a chart
Shop rule: delivery minutes above 30 turn red. Readers see risk without reading every number.
दुकान नियम: 30 पेक्षा जास्त delivery मिनिटे लाल होतात. वाचक प्रत्येक number न वाचता risk बघतो.
दुकान नियम: 30 से ज़्यादा delivery मिनट लाल होते हैं. पाठक हर number पढ़े बिना risk देखता है.
- Select the bar chart.
- Columns → Colour → fx → Rules on
[Delivery Mins]or on a status measure. - Or build
Delivery Colour = IF ( [Delivery Mins] >= 30, "#E11D48", "#16A34A" )and use Field value. - Title the visual so “red = late” is written, not guessed.
What you see: Mumbai’s late bar turns red. Pune’s fast bar stays green. Totals still add the minutes; colour is not a filter.
Mistakes and calm fixes
| Symptom | Likely cause | Fix |
|---|---|---|
| Nothing turns red | Rule on wrong field / blank measure | Test the measure on a card first |
| Total row looks wild | Rule applied where grain differs | Format values, review totals |
| Too many colours | Every column decorated | One KPI colour story per page |
| Red means good | Direction never defined | Document “high is bad” for times |
Ravindra Bagale's Tip
Interview line: “I start with Rules for SLAs, Gradient for heat maps, and a colour measure when the logic is business-specific.” Then point at fx. Got it?
Ravindra Bagale's Tip – मराठी
Interview line: “मी SLA साठी Rules ने सुरू करतो, heat maps साठी Gradient, आणि business-specific logic असताना colour measure.” नंतर fx कडे बोट दाखवा. समजलं का?
Ravindra Bagale's Tip – हिंदी
Interview line: “मैं SLA के लिए Rules से शुरू करता हूँ, heat maps के लिए Gradient, और business-specific logic होने पर colour measure.” फिर fx की ओर इशारा करो. समझ में आया?
Practice task
- Build the three-row delivery table.
- Rule: ≥ 30 red.
- Add a gradient on Sales for contrast.
- Write the sentence each colour means.
Got it? Conditional formatting paints the story — rules, gradient, or a colour measure. Next: hierarchy and drill down. Let us go ahead.
समजलं का? Conditional formatting गोष्ट रंगते — rules, gradient, किंवा colour measure. पुढे: hierarchy आणि drill down. आता पुढे जाऊया.
समझ में आया? Conditional formatting कहानी रंगता है — rules, gradient, या colour measure. आगे: hierarchy और drill down. अब आगे बढ़ते हैं.
Frequently asked questions
Where is conditional formatting?
On tables and matrices under Cell elements, and on many colour settings via the fx button. Titles can use fx too.
Rules vs gradient?
Rules are hard bands (above 30 = red). Gradient is a smooth scale from low to high.
What is field value?
The colour comes from a measure or column that already stores a colour code or a category the format maps.
Why is nothing red?
The rule tests the wrong field, the measure is blank, or you formatted values while looking at a total row.
Icons and data bars?
Same Cell elements menu. Use icons for status, data bars for length inside the cell.
Course lessons?
Conditional formatting rules and KPI-based colouring, in the dynamic text and colour chapter.