Create calculation groups

Calculation groups can significantly reduce the number of redundant measures you have to create, by allowing you define DAX expressions as calculation items that apply to the existing measures in your model. More information about calculation groups is available in the Calculation groups article.

Add a new calculation group

In Power BI Desktop when you have a local model open, navigate to Model view and select the Calculation group button in the ribbon. If you're not already in Model explorer, the Data pane opens to the Model view.

If the discourage implicit measures property is turned off, you're prompted with a dialog window to turn it on to enabling creation of the calculation group.

An implicit measure occurs when, in the Report view, you use a data column from the Data pane directly in the visual. The visual allows you to aggregate it as a SUM, AVERAGE, MIN, MAX, or some other basic aggregation, which becomes an implicit measure. Creating a calculation group discourages the creation of such implicit measures by no longer showing the summation symbol next to the data columns in the Data pane, and blocks adding the data columns to the visuals directly on aggregation axis or as values. Existing implicit measures already created in visuals will continue to work. The Discourage implicit measures property must be enabled because calculation items don't apply to implicit measures. Calculation items only apply to measures or explicit measures.

A measure or explicit measure occurs when you create a New measure and define the DAX expression to aggregate a data column. Explicit measures can also have conditional logic and filters, taking full advantage of what you can do with DAX. Tutorial: You can learn how to Create your own measures in Power BI Desktop.

Note

Calculation items can be created in a way that they ignore an explicit measure by the measure name for scenarios when you have a measure you do not want the calculation item to change.

Once you select Yes, or if you already have enabled the discourage implicit measures property, a calculation group is added, and you can start defining the DAX expression of the first calculation item in the DAX formula bar.

SELECTEDMEASURE() is a DAX function that acts as a placeholder for the measure to which the calculation item will apply. You can learn about the SELECTEDMEASURE DAX function from its article.

Time intelligence example

There's a Time Intelligence example of a calculation group available at in the Calculation groups in Analysis Services tabular models article, which we can use to populate some calculation items. The example can be added to any model with values by date and a Date table that is marked as a date table, or you can download the Adventure Works DW 2020 PBIX from DAX sample model - DAX.

Rename a calculation group

To rename the calculation group, double-click it in the Data pane, or you can select it and use the Properties pane.

Rename a calculation group column

To rename the calculation group column, double-click it in the Data pane, or you can select it and use the Properties pane. The column you select is the column you'll use on visuals or in slicers to apply a specific calculation item.

Rename a calculation item

The first calculation item was created as SELECTEDMEASURE() so it can be renamed by double-clicking or using the Properties pane as well.

Create additional calculation items

To create additional calculation items, you can use the right-click context menu of the Calculation items section or the calculation group itself and choose New calculation item, or use the Properties pane of the Calculation items section.

Once I've added all the Time intelligence calculation items, my calculation group looks like the following image.

Notice the red triangle icons indicating errors. The errors are there because the example DAX expressions use the Date table called DimDate, so I need to update the DAX expressions to use the name Date instead. The following image shows the DAX expression before the correction.

Once I make the correction to the DAX expression, the error disappears.

Once I make the corrections for each of the errors in the calculation items, the red triangle warning icons no longer appear.

Reorder calculation items

To reorder the calculation items in whatever logical way you prefer, you can select the Calculation items section in the Properties pane, or right-click context menu of the calculation item to move it up or down in the list.

Add a dynamic format string to a calculation item

Calculation items use the underlying measure formatting by default. We might want to instead display YOY% as a percentage. To do so, select the YOY% calculation item, then turn on Dynamic format string in the properties pane, which allows you to specify a DAX expression to create a format string. For this example, it doesn’t require any conditional elements, so simply #,##0.00% will change the format to a percentage when this calculation item is applied, as shown in the following image.

Using the calculation group in reports

To use your new calculation group in a Report, go to the Report view, create a Matrix visual and add the following:

1. Month column from the Date table to the Rows
2. Time Calculation from the Time Intelligence calculation group to the Columns
3. Orders measure to the Values

Note

If the measure Orders is not created in the mode, you can use a different measure or go to the ribbon and choose New Measure with this DAX expression.

Orders = DISTINCTCOUNT('Sales Order'[Sales Order])

The following image shows building a visual.

Calculation items on the Columns in the Matrix visual are showing the measure Orders grouped by each of the calculation items. You can also apply an individual calculation item to multiple measures by adding the calculation group column to a Slicer visual.

Using the calculation item in measures

You can create a new measure with a DAX expression that will utilize a calculation item on a specific measure.

To create an [Orders YOY%] measure you can use the calculation item with CALCULATE.

Orders YOY% = 
    CALCULATE(
        [Orders],
        'Time Intelligence'[Time Calculation] = "YOY%"
    )

Setting calculation group precedence

Finally, if you add additional calculation groups to the model and you want to specify the order in which they apply to measures, you can adjust the calculation group precedence in the Calculation groups section properties pane, as shown in the following image.

You can learn more about calculation groups precedence in the Calculation groups in Analysis Services tabular models article.

Related content

The following articles describe more about data models, and also describe DirectQuery in detail.

• Work with Modeling view in Power BI Desktop
• Automatic aggregations
• Use composite models in Power BI Desktop
• Manage storage mode in Power BI Desktop
• Many-to-many relationships in Power BI Desktop

DirectQuery articles:

• DirectQuery in Power BI
• Power BI data sources