How to create a dynamic KPI in Power BI: step by step

João Barros 13 de September de 2026 4 min read

This tutorial shows how to create a dynamic KPI in Power BI that displays the current value, the target, the percentage variance and a traffic light (green/yellow/red). A dynamic KPI helps focus objectives, compare against targets and quickly identify deviations in the report.

Prerequisites

Power BI Desktop installed
A fact table with columns: Date, Value, and a Targets table with Date and Target
Basic knowledge of Power Query and DAX

Step 1: Prepare data and relationships

Import your fact table (for example Sales) and the Targets table into Power BI. Create a calendar table if you don’t already have one. Ensure proper relationships between the calendar Date and the other tables.

Step 2: Create basic measures in DAX

Let’s create measures for Current Value and Total Target. These measures perform the correct aggregation and respect filter context (date slicer, product, etc.).

Valor Actual = SUM(Sales[Value])
Meta Total = SUM(Metas[Target])

Step 3: Variance measure and formatting

Create a measure that calculates the difference and the percentage between Current Value and Total Target. This measure will be used in the card and in the color rules.

Variação = [Valor Actual] - [Meta Total]
Variação % = DIVIDE([Variação], [Meta Total], 0)

Step 4: Create a status indicator (traffic light) with logic

Decide the business rules: for example, Green when >= 100% of target, Yellow between 90% and 100%, Red below 90%. Create a measure that returns text or a color code.

Estado KPI = 
VAR pct = [Variação %]
RETURN
SWITCH(
    TRUE(),
    pct >= 1, "Verde",
    pct >= 0.9, "Amarelo",
    "Vermelho"
)

Step 5: Create a color measure (HEX) to use in conditional formatting

To format visuals, create a measure that returns HEX values. This allows applying the color dynamically on the card or a shape.

Cor KPI = 
SWITCH(
    [Estado KPI],
    "Verde", "#2ECC71",
    "Amarelo", "#F1C40F",
    "Vermelho", "#E74C3C",
    "#AAAAAA"
)

Step 6: Build the KPI visual

Insert three Card visuals (or a native KPI visual). In the first Card place the measure [Valor Actual], in the second Card place [Meta Total] and in the third place [Variação %] formatted as a percentage. For the traffic light use a shape visual or a card with [Estado KPI].

Step 7: Apply conditional formatting

To apply dynamic color, select the visual (e.g.: Card for Current Value) > Format > Data label (or Background) > Conditional formatting > Format by Field value and choose the measure [Cor KPI]. Repeat for other elements (borders, background, icon).

Step 8: Enhance with tooltips and configurable thresholds

Allow the user to adjust limits by creating a parameter table or using What-if parameter. Create measures that read those parameters instead of fixed values (0.9 and 1) to make the KPI configurable.

Estado KPI Param = 
VAR lim_amarelo = SELECTEDVALUE(ParamTable[AmareloThreshold], 0.9)
VAR lim_verde = SELECTEDVALUE(ParamTable[VerdeThreshold], 1)
VAR pct = [Variação %]
RETURN
SWITCH(
    TRUE(),
    pct >= lim_verde, "Verde",
    pct >= lim_amarelo, "Amarelo",
    "Vermelho"
)

Verify the result

Filter by different periods in the Date slicer and confirm that: 1) [Valor Actual], [Meta Total] and [Variação %] update correctly; 2) the traffic light text changes as expected; 3) the colors applied by conditional formatting change according to the status. Test boundaries exactly at 90% and 100% to validate the logic.

Conclusion

You now have a dynamic KPI in Power BI that shows value, target, variance and a configurable visual traffic light. Next steps: add drillthrough for details, use a custom visual with icons and publish to Power BI Service. Tip: keep the metrics and thresholds documented so the team knows how to interpret the traffic light.