Fun with Calculation Groups

CHOOSING MEASURES TO AGGREGATE

I recently built a margin forecasting tool and the margin calculation itself consisted of the sales amount minus around fifteen different cost calculation measures, some of which were complex. One of the reporting requirements was to have user control over which costs were included in the calculation.

  1. Add the different cost measures to a calculation group as calculation items

NEW CUSTOMER METRICS

Another fun use for calculation groups is to wrap the SELECTEDMEASURE() in CALCULATE with USERELATIONSHIP as the modifier. This lets you give users the ability to enable different model relationships for report calculations once you add the calculation items to a slicer. In effect giving users the ability to dynamically alter the model.

Credit: https://www.daxpatterns.com/new-and-returning-customers/

COLUMN SWITCHING

Another fairly straightforward technique mimics column switching with a slicer. You can, in a way, replicate a categorical column of attributes with a calculation group if there aren’t too many unique values. Let’s say you have a column which categorises products, you can replicate it in effect in a calculation group by replicating each unique column value as a filter. So you would create the first calculation item as:

Each calculation item acts like a column value that mimics one of the values in an actual column

CALCULATED COLUMN REPLACEMENT

Anywhere you read about choosing between a calculated column and a measure, you will invariably come across the following key criteria for a calculated column: If the result needs to be shown in a slicer (or legend, axis, ect) then you need to create a column (either in Power Query or DAX). With calculation groups this is not strictly true anymore as shown in the examples. Calculation groups allow you too selectively apply different filters (including complex and custom ones: https://tenfingers.medium.com/dynamic-segmentation-with-calculation-groups-385a91156f5b) and measures to a report through slicers, legends, axis ect.

Calculated Column
Calculation Group
Calculation Group Vs Calculated Column slicing a matrix
Size of Transaction Grade Calculated Column
Size of Transaction Grade calculation group achieving the same result

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store