Labs · Power BI

Lab: Colour Bars Red or Green with a DAX Measure and Write a Dynamic Title

By Ravindra Bagale · Last updated: 8 October 2026

Suppose we are…

Suppose we are preparing the Croma monthly review. Each city has a sales target in pbi_cities.csv: Mumbai 400,000, Pune 350,000, Nagpur 250,000, Nashik 300,000. The director wants the city chart to show at a glance who met the target, and a title that updates itself when the data changes. The numbers are sample data made up for practice.

Goal of this lab

By the end you will have:

• Measures for Target, a bar colour and the number of cities below target.
• A bar chart with green bars for Mumbai and Nagpur and red bars for Pune and Nashik.
• A title that reads "2 of 4 cities below target", written by DAX.

What you need (all free)

• Power BI Desktop and a file with the star schema from Lab 11 (pbi_sales, pbi_cities with Target, Total Sales).
• Sample data if you need to rebuild: Download pbi_sales.csv (24 orders) Download pbi_cities.csv (with Target) Download pbi_products.csv
• 30 minutes.

The data: before and after

Before. Every bar is the same colour. Good or bad? You cannot tell.

After. Green = target met, red = below target, and the title counts the red ones.

The logic behind the colours:

The formula

Four short measures:

Target Amount = SUM ( pbi_cities[Target] )

Bar Colour = IF ( [Total Sales] >= [Target Amount], "#2E9E5B", "#D64545" )

Cities Below Target =
COUNTROWS ( FILTER ( VALUES ( pbi_cities[City] ), [Total Sales] < [Target Amount] ) )

Chart Title =
[Cities Below Target] & " of " & COUNTROWS ( VALUES ( pbi_cities[City] ) ) & " cities below target"

• Bar Colour returns a text colour code. For Pune: 346,000 ≥ 350,000? No, so it returns red #D64545.
• Cities Below Target: VALUES gives the list of cities in the current filter, FILTER keeps those below target (Pune, Nashik), and COUNTROWS counts them: 2.
• Chart Title joins numbers and text with &: "2" & " of " & "4" & " cities below target".

Steps

1. Open your star-schema file and Save as Lab-20-dynamic-colour.
2. Right-click pbi_cities → New measure and create Target Amount, then Bar Colour, Cities Below Target and Chart Title from the formula box (one measure at a time).

3. Make a Clustered bar chart: Y-axis pbi_cities[City], X-axis Total Sales. Turn on data labels.

   👁 What you should see: all bars in one colour, like the Before image.

4. Select the chart → Format visual → Visual → Bars → Colors. Click the fx button next to the colour.

5. In the window, set Format style to Field value, and What field should we base this on? to Bar Colour. Click OK.

   👁 What you should see: Mumbai and Nagpur green, Pune and Nashik red.

6. Go to Format visual → General → Title, click the fx next to Text, choose Field value and Chart Title. Click OK.

   👁 What you should see: the title reads 2 of 4 cities below target.

7. Add a Table with City, Total Sales, Target Amount and a gap measure Gap = [Total Sales] - [Target Amount].

   👁 What you should see: Gap: Mumbai 39,000, Pune -4,000, Nagpur 10,000, Nashik -43,000.

8. Test the dynamic part. Add a Slicer with pbi_cities[Region] and click Konkan.

   👁 What you should see: one green bar (Mumbai) and the title 0 of 1 cities below target. Clear the slicer.

9. Save.

Common mistakes

Self-check checklist

Try-at-home challenge

Add a Product slicer and make a second title that says which product is selected: "Sales for Laptop" when one product is selected, and "Sales for all products" when none or several are selected. Hint: SELECTEDVALUE.

Product Title = "Sales for " & SELECTEDVALUE ( pbi_products[Product], "all products" )

Samjla ka? A colour measure returns a hex code; fx → Field value applies it; a text measure writes the title. Aata pudhe jaauya: switch the chart's measure with a field parameter and add a Top N selector.