Calculation groups can significantly reduce the number of redundant measures by grouping common measure expressions as calculation items. Calculation groups are supported in tabular models at the 1500 and higher compatibility levels.

These are most commonly used in time intelligence measures and to reduce the redundant complex codes. It can significantly reduce the number of measures and code required, improve the performance and simplify the overall solution.

Where can you use calc groups?

Repeated Logic:

Many times we write the same logic for multiple measures, for example, when you have to calculate YOY, Last year’s previous Week, Currency Conversion, Unit Conversion, etc. With the help of Calculation Groups, we can create reusable logic for such conditions and situations.

Sales Order PY := CALCULATE( [Sales Order], ‘Time Travel’[Calc Item] = “PY”)

Where “Time Travel” is a Calc Group and “Calc Item” is a column in that Calc Group.
“PY” is a calculation item in Calc Group whose code is shown below:

Calculate( SelectedMeasure(), SAMEPERIODLASTYEAR( ‘Time’[Date])) 

Here “Time” is a table that is joined to the Fact table where we have the [Sales Order] and “Date”  is a column in the Time table which is connected to ‘Sales Order’[Created Date]

A few more examples include :

• PY Week: Compare the week in subject with the same week in the previous year
• Prior Week: Compare the week’s subjects with the previous week
• Last 10 Week Avg: In every week we can show the average of the last 10 weeks

Measure Formating

We can apply formatting to the selected measures, such as adding a symbol, percentage sign etc. This ensures that reports have the expected formatting.

Change measure output based on the filter selection

When we have a filter dimension and we decide to change the logic based on the selections on the filter, we implement switch and if-else logic. Here for each measure, we will have to create a switch logic. Another way is to implement it using calculation groups.

Below is an example:

Let’s say we have 3 measures : [Net Value] , [Total Value], [Count]

And we have to get  “PY Week”, “Prior Week”, and “Last 10 Week Avg” values for the above measures. And then we should show one table in Power BI which displays the above 3 measures [Net Value], [Total Value], [Count] and changes the logic based on one of the selected values out of  “PY Week”, “Prior Week”, “Last 10 Week Avg”.

In this case, we would normally write a Switch case logic which would look like:

VAR selectedFilterValues = SelectedValue([Comparison])
Switch( selectedFilterValues, “PY Week”,  <logic to calculated PY Week>
            “Prior Week”,  <logic to calculate PriorWeek> “Last 10 Week Avg”,  <Last 10 Week Avg>)

We would implement the same logic for all the measures and then use these measures in the report. But the performance will be very slow with a lot of redundancy. This can be changed using calc groups as follows:

• Create a calc group and 3 items to it “PY Week”, “Prior Week”, “Last 10 Week Avg”
• Now add the generic logic to each of the measures Eg. for PY Week

Calculate( SelectedMeasure(),  <logic to calculated PY Week>)

• Drag the original [Net Value] , [Total Value], [Count] measure in the table in Power BI report.
• Drag the Calculation Items in the dashboard as filters.

And now if you change the selection in the filter, all the measures on the dashboard will show results as per the selected Calculation item.
This will work not only for these 3 measures but for all the measures in the report. If we don’t want certain visuals to be impacted, we can modify the interactions in Power BI.

Comparing Measures

If we want to compare the results of two or more measures we can do that as well using calc groups.

For example comparison of PY to PPY

[Sales Order PY] – [Sales Order PPY]

With this type of logic, we can move in calc groups and simplify the logic.