In Power BI, "Calculation Groups" are advanced functionality that allows you to create a set of calculated measures that can be applied to other measures in a model. These calculation groups are especially useful for applying common transformations or calculations to multiple measures without having to rewrite or duplicate calculation logic for each individual measure.
By Vicente Antonio Juan Magallanes -
30th October 2023 - fp20 analytics
Advantages and benefits of use
Logic Reuse: For example, if you have many measures and you want to view each of them in terms of "Percentage change from previous year", instead of creating a separate measure for each one, you can create a calculation group that applies this logic to all selected measures.
Flexibility: Calculation groups can contain multiple calculation items. Each calculation item represents a specific logic or formula that can be applied to the measurements. Users can easily select which calculation item they want to apply from a drop-down list in a Power BI report.
Simplified Management: By centralizing calculation logic in one group, any necessary changes only need to be made in one place, making it easier to manage and maintain data models.
First we must activate the latest update:
Go to the ribbon and select the option called File
Then in the dialog window we select, Options and settings
After that we choose options
After that we go to the Previous functions section
We activate the option with the check
The three approaches to create Calculation Groups in Power BI
We continue in the model view, on the right side, in the panel option, we are located where it says data
After them we click a little further down where the word model indicates
We select with the right mouse button where it says Calculation groups
We will have a small dialog window in which we will have the option to create.
We follow the model view, in the ribbon at the top, we can see that it says Calculation Group.
We can do this by primary clicking on Calculation Groups.
After them we go to properties where we have the button + New calculation group
Creation of the Calculation Group
Being clear about where we can create them, we select the option that is easiest for us, and when we finally select 'Create calculation group', this alert window will appear.
When you add a "Calculation Group", "implicit measurements" will be discouraged in this model. This means that you will need to create "explicit measures" to add columns of data. In other words, instead of relying on automatic measures generated by Power BI, you'll need to create custom measures to perform specific aggregations and calculations in your data model.
Differences between Implicit and Explicit measures
Implicit Measures: Implicit measures are measures that are automatically created in Power BI without user intervention. For example, when you drag a numeric field into a visualization, Power BI automatically creates an implicit summation measure for that field. These measures are generated automatically to simplify the reporting process.
Explicit Measures: Explicit measures are measures that the user creates manually to perform specific calculations in the model. The user defines the calculation logic, such as sums, averages, percentages, etc., explicitly.
Creation of the Calculation Group
With these clear points we proceed and give YES, after them the Calculation Groups table will give us the result on the right side below the semantic model
As we can see in this structure we first have a table, to which we can edit a custom name, for example “Calculation Groups”.
Within this table we have a column, which we can also customize
We also have a calculation element that is created by default, we can edit its name
Now we proceed to add another additional calculation element.
Now for this example we will use the following average which represents the previous year, but instead of a created measure, the magic is in placing the SELECTEDMEASURE(), so that it calculates any measure we want based on the logic that is in the formula , with just one formula.
View the result of our measurement Calculation Groups
We create a visual object of type matrix
We add the Year columns and the sales measure
Now, let's add our column, from the calculation groups table that we just created.
Now let's add another measure to our matrix called sales volume. As we can see in this case we can visualize creating the calculation with the same previous formula but now also with sales volume.
With this example we can observe the amount of time intelligence measures that we can save in creation, under the same logic, with Calculation Groups.
Using calculation groups in Power BI offers an efficient solution to reduce the number of time intelligence measures we need to create in a data model. Instead of repeating the same calculation logic across multiple measures, calculation groups allow you to centralize this logic and apply it to multiple measures, saving time and simplifying model management. This means BI professionals can work more efficiently and effectively when creating and maintaining complex data models in Power BI.