Optimize Dynamic KPIs in Power BI with Calculation Groups
Have you ever worked on a report containing over 40 different KPIs? If you have, you probably faced the issue of having too many measures, as each KPI requires extra time for intelligence calculations such as its value in the previous year, variation over periods, and more. This signifies that you need to create a new measure for each KPI, and when you have to add a new KPI to the report, you have to do it all over again.
Calculation groups were created to solve this issue, but they still present some limitations in what you can achieve with them. For example, let’s say that you need to put the actual value of a KPI, its value in the previous year, and the difference between both on a card. You were smart and configured time intelligence calculation groups, but now you can’t use them because you can’t make Power BI use different calculation items in the same visual.
With the technique explained below, we will leverage the power of calculation groups with some clever conditional logic so you can make your own life easier. Even more, you will be able to add new KPIs and create new pages or visuals in a breeze, only needing to add a single DAX formula and change the filter in a visual, all the while maintaining the business logic that affects the rest of the KPIs and without having to spend hours configuring a single visual.
Creating a KPI table
The first thing we have to do is to procure a table that serves as a full list of KPIs in our report. This table will be essential for the solution, but you can leverage its existence even more by using it as a dictionary or even to keep documentation.
The table needs to have a format like this:

ID and Measure Name are self-explanatory. The rest of the columns are up to you. You can add a description, comments, or more defining characteristics. For this solution, the Format column will not be used, but the ‘Negative is good’ column will let us apply green and red colors dynamically, depending on each measure’s logic (for example, reducing the Cost of Goods is a positive thing, so we want to show it with a green color wherever we see the change over time).
Remember that the first step when you want to add a new KPI is to add a new row to this table with the new measure’s name and all the rest of its characteristics.
Preparing the Auxiliary Measures
These measures are simple and will be mere placeholders, but it’s imperative to have them as they will control the calculation groups’ logic.
[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 = ""
Title and Colour must be forced to empty text so we can use them as conditional formatting in the appropriate fields later on. Each measure corresponds to a section of a card visual: callout value, title of the reference label, value of the reference label, detail of the reference label, and the conditional color we will use for the reference label as well.
We also need some basic measures that give the value of each of our KPIs. In this case, COGS and Sales measures are simple sums of the corresponding columns in the facts table.
Preparing the Card Visual
For the next step, we will create a new visual card and spend some time to make it look nice. The title field of the visual will be dynamic, and we will configure it to take the first value of the Measure Name column in our KPIs list table.

Just so we can identify the fields, the measures were given some temporary values. Remember to put dyn_Actual in the main field well, then dyn_Var_Value in the reference label field well, and finally both dyn_Var_Title and dyn_Var_Detail in the corresponding title and detail field wells of the reference label.
Delving into the Calculation Groups
It’s time to create all the magic. We are going to make a new calculation group and add the basic calculation items that we are going to use. For this solution, we are utilizing a feature that works only within the calculation groups, which is to reference the calculation items so you can create an easy-to-understand chain logic.
The first calculation item is the one that is going to select which measure is to be calculated depending on the filter affecting the card visual.
[DAX CODE] valueActual = SWITCH (
SELECTEDVALUE ( _Measures[Measure Name] ),
"COGS", [COGS],
"Sales", [Sales])
When you want to incorporate a new KPI, you will need to add it to this SWITCH statement. The pair must be: the Measure Name (same as in the KPIs list table) and then the reference to the corresponding DAX measure. Based on this first calculation item, we can now create time intelligence items using this one as a reference.
[DAX CODE] valueLastYear =
CALCULATE (
SELECTEDMEASURE (),
'Smart KPIs'[Calculation] = "valueActual",
SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)
[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
[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
)
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 checks which measure is being executed, which will be different for each section of the card. When we are in the callout value section, we return the actual value of the measure. When we are in the reference label title, we return an arrow indicating an upward or downward trend based on the absolute variation between the actual value and the value of the previous year. When we are in the reference label detail, we return the percentage variation to provide more context to the numbers shown. Lastly, if the code is executing the dyn_Color conditional formatting, we start by inverting the variation depending on the value of the “Negative is good” column for the selected measure and then return a red, black, or green color hexadecimal code.
Now we can add a couple of filters to our card visual and watch the magic unfold.

‘Calculation’ is the name of the calculation group column. It is looking quite good, but the formatting needs a bit of work still. We want to show both the callout value and the reference label value in millions of euros, and we don’t want to show negative symbols because the dynamic color and the arrows will provide enough visual cues. We don’t want to confuse the users with unnecessary symbols.
For that, we can use custom format strings in the corresponding field settings. For example, the callout value format string will be “##0,,.0 M€” so the number is automatically divided by a million and “M€” is added at the end. In the reference label format string, we will use “##0,,.00 M€;##0,,.00 M€;0 €” so both positive and negative values are shown the same, and if the value is zero, we don’t show the ‘M’ for millions. Similarly, we will use “(0.0%);(0.0%);0%” for the reference label detail format string so the percentage is shown in parentheses, without the negative symbol, and with only a single decimal place so it doesn’t occupy too much space.
With the addition of the dyn_Color measure in the conditional formatting, we have finally created the ‘smart KPI card’.

The building block is ready
This looked like a lot of work, but now we have a card visual that is fully configured. If we want to add another one with a different KPI, we just need to copy and paste and select a different Measure Name in the visual level filter. We can even see how they change dynamically when we interact with a date slicer.

The color changes depending on the positive or negative variation and the value of the ‘Negative is good’ characteristic is assigned to each measure, automatically and dynamically. We just needed to provide the logic in a single calculation item that will be shared across all the measures in our report.
Wrapping up
This technique is highly customizable, allowing you to include any extra logic you need within the calculation group. You can utilize the format string element of the calculation items to use the ‘Format’ column in the KPIs list table we prepared at the beginning. This way, you can change the format used depending on the selected measure. As a result, you don’t need to configure the format string in each of the card field wells, and all the logic remains in a single place.
Another interesting application of the calculation item format string is adapting the rounding to millions, thousands, or units depending on the actual value of the measure. This allows you to keep ‘Units sold’ displayed as units while showing ‘Sales’ in millions of euros simultaneously, without having to configure the format string of each card differently.
We highly recommend familiarizing yourself with all the steps shown in this article to unlock a world of possibilities available to you as a Power BI developer, thanks to the calculation groups.
👉 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/