Calculation Groups

Calculation groups is a new option within the Power BI Desktop application that allows us to implement DAX calculation groups within the data model. They serve to simplify calculations when we use similar DAX expressions, where only the variable (expression) that appears in them varies. By adding them, the model is automatically optimized by removing implicit measures.

To be able to use this option, you must first turn it on in the settings, in the Preview Features section, and then restart Power BI Desktop.

We start from a model consisting of the Items and Transactions tables to which we added the Calendar table and connected it to transactions. First, in the Transactions table, we will create the following measures:

SalesRevenue = SUMX(Transactions,Transactions[QTY]*RELATED(Items[SPC]))
COGS = SUMX(Transactions,Transactions[QTY]*RELATED(Items[PPC]))
GrossMargin = Transactions[SalesRevenue]-Transactions[COGS]

In the Calendar table, we will add two calculated columns Year and Month that calculate the year and month of the date column.

To create a new calculation group, first select Model view. In the Home ribbon, click the Calculation Group button. A message will appear that implicit measures will be disabled. Confirm the action by clicking on Yes. A calculation group will appear in the Model Explorer.

A calculation group consists of several calculated items. We will declare the following calculated items:

SALES CY = SELECTEDMEASURE()
SALES PY = CALCULATE(SELECTEDMEASURE(),SAMEPERIODLASTYEAR(‘Calendar'[Date]))

We will change the name of the calculation group to CG. Now we can create a visualization in which we will use it. Let’s add the Matrix visual, and then to its Rows two calculated columns Year and Month that show the year and month of the date in the Calendar table. Add CG to the Columns, and the measure for which we want to perform the calculation to the Values field. In this example it is Sales Revenue.

Now, by simply replacing the measure in the Values field, we can easily display current sales and sales in the same period of the previous year for each of the measures created just now.