Optimize Dynamic KPIs in Power BI with Calculation Groups

Optimize Dynamic KPIs in Power BI with Calculation Groups

Too Many KPIs, Too Many Measures

Have you ever built a report with more than 40 KPIs? If so, you know the pain: each KPI needs its own measure plus extra calculations for year-over-year comparisons, period variations, and more. When you add a new KPI, you repeat the whole process.

Calculation groups solve much of this problem, but they also come with limits. Imagine you want to show three values on one card: the actual KPI, last year’s value, and the difference. Time intelligence calculation groups help with part of this, but Power BI does not let you display multiple calculation items in the same visual.

Why Calculation Groups Matter

Calculation groups were designed to reduce this complexity. They let you define reusable logic instead of duplicating measures. However, they also have limits. Even if you’ve configured time intelligence calculation groups, you can’t apply multiple calculation items to the same visual. That’s a problem for KPI reporting.

The Goal

In this article, we’ll combine calculation groups with conditional logic. This technique allows you to:

• Display dynamic KPIs in one card.
• Reuse the same structure across multiple visuals.
• Add new KPIs easily by writing just one formula.
• Keep consistent business logic across all measures.

Step 1: Create a KPI Table

The foundation of this approach is a dedicated KPI table. It contains every KPI in your report and can also serve as documentation or a dictionary.
A simple structure might include:

• ID and Measure Name.
• Optional fields like Description, Comments, or Format.
• A column for Negative is good to handle conditional colors. For example, a drop in COGS is positive, so you display it in green.

This last column is especially useful. It lets you apply green or red colors dynamically depending on whether the KPI going up or down is good for the business. For example, an increase in Sales should be green, but a decrease in COGS should also be green. Whenever you add a new KPI, just add a row to this table with its name and attributes.

Step 2: Prepare Auxiliary Measures

Next, create placeholder measures. These control how the calculation group logic works.

[DAX CODE] dyn_Actual = BLANK ()
[DAX CODE] dyn_Var_Title = ""
[DAX CODE] dyn_Var_Value = BLANK ()
[DAX CODE] dyn_Var_Detail = BLANK ()
[DAX CODE] dyn_Colour = ""

Each one maps to a section of the card visual:

dyn_Actual → main callout value.
dyn_Var_Title → reference label title.
dyn_Var_Value → reference label value.
dyn_Var_Detail → reference label detail.
dyn_Colour → conditional color for labels.

You also need your base KPI measures. For example, [COGS] and [Sales] as sums of their fact table columns.

Step 3: Build the Card Visual

Create a card visual. Set the title to display the first value of the Measure Name column. Assign the measures:

Set the title to show the first value from Measure Name in the KPI table.

Place dyn_Actual in the callout field.
Place dyn_Var_Value in the reference label.
Place dyn_Var_Title and dyn_Var_Detail in the title and detail fields of the reference label.

At this stage, the values are placeholders. The real logic will come from calculation groups.

Step 4: Create the Calculation Group

Actual Value
First, create a calculation item to select the right measure based on the KPI filter.

valueActual =
SWITCH (
    SELECTEDVALUE ( _Measures[Measure Name] ),
    "COGS", [COGS],
    "Sales", [Sales]
)

When adding a new KPI, extend this SWITCH with the KPI name and its corresponding measure.

Last Year Value
Next, reference the actual value to calculate last year’s result:

[DAX CODE] valueLastYear =
CALCULATE (
    SELECTEDMEASURE (),
    'Smart KPIs'[Calculation] = "valueActual",
    SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)

Absolute Variation
Now calculate the absolute difference between current and last year:

[DAX CODE] valueVariationAbs =
VAR __VALUE_ACTUAL =
    CALCULATE (
        SELECTEDMEASURE (),
        'Smart KPIs'[Calculation] = "valueActual"
    )
VAR __VALUE_LAST_YEAR =
    CALCULATE (
        SELECTEDMEASURE (),
        'Smart KPIs'[Calculation] = "valueLastYear"
    )
RETURN
    __VALUE_ACTUAL - __VALUE_LAST_YEAR

Percentage Variation
Finally, compute the percentage difference:

[DAX CODE] valueVariationPerc =
VAR __VALUE_ACTUAL =
    CALCULATE (
        SELECTEDMEASURE (),
        'Smart KPIs'[Calculation] = "valueActual"
    )
VAR __VALUE_LAST_YEAR =
    CALCULATE (
        SELECTEDMEASURE (),
        'Smart KPIs'[Calculation] = "valueLastYear"
    )
RETURN
    DIVIDE (
        __VALUE_ACTUAL - __VALUE_LAST_YEAR,
        __VALUE_LAST_YEAR
    )

Step 5: Control Card Output

As you can see in the code above, SELECTEDMEASURE() will always reference the original dyn_Actual or other blank DAX measures. This is not important, as when Power BI applies the valueActual calculation item, it will override the blank value for the corresponding measure that we are indicating with a filter. Finally, we can create a new calculation item that references all of the previous items and controls which one is used in each section of the card visual.

[DAX CODE] Card =
VAR __VALUE_VAR_ABS =
    CALCULATE ( SELECTEDMEASURE (), 'Smart KPIs'[Calculation] = "valueVariationAbs" )
RETURN
    SWITCH (
        TRUE (),
        ISSELECTEDMEASURE ( [dyn_Actual] ),
            CALCULATE ( SELECTEDMEASURE (), 'Smart KPIs'[Calculation] = "valueActual" ),
        ISSELECTEDMEASURE ( [dyn_Var_Title] ),
            SWITCH (
                TRUE (),
                __VALUE_VAR_ABS < 0, "⠀▼ ",
                __VALUE_VAR_ABS = 0, "⠀",
                __VALUE_VAR_ABS > 0, "⠀▲ "
            ),
        ISSELECTEDMEASURE ( [dyn_Var_Value] ),
            __VALUE_VAR_ABS,
        ISSELECTEDMEASURE ( [dyn_Var_Detail] ),
            CALCULATE ( SELECTEDMEASURE (), 'Smart KPIs'[Calculation] = "valueVariationPerc" ),
        ISSELECTEDMEASURE ( [dyn_Color] ),
            VAR __VALUE_ADJUSTED =
                __VALUE_VAR_ABS
                * POWER ( -1, INT ( SELECTEDVALUE ( _Measures[Negative is good] ) ) )
            RETURN
                SWITCH (
                    TRUE (),
                    __VALUE_ADJUSTED < 0, "#D64550",    -- Red
                    __VALUE_ADJUSTED = 0, "#000000",    -- Black
                    __VALUE_ADJUSTED > 0, "#6F9F2F"     -- Green
                )
    )

ISSELECTEDMEASURE is the key to making the card dynamic. It tells Power BI which placeholder measure is running and applies the right logic: actual value, arrow, variation, percentage, or color. With this single calculation item, you can control every part of the card in a consistent way. Once the logic is in place, move to the visual. Add a few filters to the card, and its behavior becomes dynamic and fully automated.

‘Calculation’ is the name of the calculation group column. The card already looks good, but the formatting still needs improvement. We want both the callout value and the reference label value displayed in millions of euros. Negative symbols are unnecessary, because arrows and dynamic colors already show the trend. Removing those symbols avoids confusing users with redundant cues.

For that, we can use custom format strings in the field settings.

• Callout value: Use ##0,,.0 M€. This divides the number by one million and adds “M€” at the end.
• Reference label value: Use ##0,,.00 M€;##0,,.00 M€;0 €. Both positive and negative values appear the same. If the value is zero, the “M” for millions is hidden.
• Reference label detail: Use (0.0%);(0.0%);0%. Percentages appear in parentheses, without negative signs, and with one decimal place so they don’t take up too much space.

With the addition of the dyn_Color measure in the conditional formatting, we have finally created the ‘smart KPI card’.

The Result: A Smart KPI Card

You now have a dynamic card that adapts to any KPI. To add another, simply copy the card and change the Measure Name filter. The logic automatically adjusts, and slicers interact smoothly.
The arrows and colors respond dynamically:

• Green for positive outcomes.
• Red for negative.
• Black for neutral.

And the “Negative is good” flag ensures the color logic matches the KPI’s business meaning.

Wrapping up

This approach creates a building block for KPI reporting. Instead of building dozens of custom visuals, you design once and reuse. The technique is highly customizable:

• Extend the KPI table with more metadata like formats or descriptions.
• Use calculation group format strings for dynamic rounding (units, thousands, or millions).
• Keep all formatting and logic centralized in one place.

Mastering this method saves hours of repetitive work and ensures consistency across all reports.

👉 If you’re interested in discovering how Microsoft Power BI can benefit your business, schedule a free workshop with our experts now: www.mdw-group.com/contact/

About MDW

MDW is a European consulting firm specialized in Data & AI. Recognized as Microsoft Partner of the Year for Azure (2024) and Partner of the Year for Data & AI (2023), MDW helps leading enterprises design, build, and operate data-driven solutions that connect Data, AI, and Business Applications.