Fun with Calculation Groups
I believe there are many Power BI users who still only only consider the classic example of Time Intelligence in regards to calculation groups but there are so many other types of use cases. The more I explore using calculation groups, the more I am astonished by the things they can do and the possible applications for them. Here are some uses I have discovered recently that I hope you will find as interesting as I do.
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.
I’m not sure how you could approach this without calculation groups or if it’s even possible? Particularly, because there are so many different combinations of costs possible. Here’s how easy it is with calculation groups:
- Add the different cost measures to a calculation group as calculation items
2. I’ve found you can aggregate all your calculation items using this familiar format where CostMetric% is my calculation group
3. Add slicer to page with cost calculation items
The user now can choose which combination of cost measures to include in the margin calculation
It’s another example of leveraging calculation groups chameleon like ability to act like a column and calculate dynamically like measures. It also shows that calculation groups work the same way in virtual DAX as they do on the report canvas in visuals. You are no longer restricted to using a disconnected table and having to return one value with SELECTEDVALUE if you want to change the measures used within a calculation.
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.
A great use case is new and lost customer reporting. For new customers, you create a column in the customer table with the customers first order date and create an inactive relationship from the first order date to your date table. Using this relationship, any date period filtered in a report filters your customer table to be only customers with their first order in the that date period.
You can then create a calculation item as follows:
You would apply this calculation item to a simple COUNTROWS( ‘Customer’ ) measure to compute the number of new customers in the selected period.
What’s great about this solution is it’s very fast because the first order date is already calculated in a column at refresh and it uses model relationships which are faster than virtual ones like TREATAS. The best part though is that you can apply it to all different measures for new customer reporting. If you want to compute new customer sales you apply the calculation item to a sales amount measure, for new custom margin you apply it to a margin measure and so on and so on, without needing to author any new measures. It’s kind of drag and drop new customer metrics. You can also add new and lost customers* to a slicer in a report and switch between metrics although its important too note that the first order date is a static column so you cannot dynamically filter results by product or other dimensions. Of course you also need to asses wether adding a calculated column to your customer table works for your model. You can create a dynamic version utilising a pattern like the following that can still be applied to different measures and that I have adapted from SQLBI’s brilliant DAX pattern :
*use a column with last order + amount of days since order for customers to be considered lost to implement the same static logic for lost 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:
And then the next calculation item would be the same but category 2 and so on and so on until all categories present in your column are done.
Now if you use the calculation groups “column” in a chart it aggregates values the same way the regular column would if you used it in a chart. Why is this useful?
Well, you can create a disconnected table and use some simple conditional logic in the calculation items to mimic the effect of switching columns with a slicer.
You can take this same concept further to create a slicer that switches a hierarchy of columns around. There are many scenarios where it is useful to switch the measure or filter applied by a calculation item according to a condition. Unfortunately, the issue I keep running into that limits these types of applications is that you cannot make the name of a calculation item dynamic or display according to what it is evaluating (or at least I dont know how too). The ability to control the display name of a calculation item would be an extremely useful addition to calculation groups in my opinion and I will submit this to the Power BI ideas site with hope that others see the usefulness.
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.
Of course, a calculated column may not get optimal compression and uses up memory in your model so it’s definitely worth considering wether you can use a calculation group in place of a new calculated column or a power query computed column. Calculation groups are small in size and as a bonus are not limited to being static filters. I will leave performance analysis to the wizards that know a lot more about it than me but as a simple test I created a calculated column on the fact table in the Contoso Sales Sample PBIX (2.2 million rows) and implemented the same functionality via a calculation group.
Applying both slicers to a matrix and viewing performance analyzer, the calculation group performed very similarily to the column. I couldnt see an obvious difference.
In terms of size, the calculation group is obviously many times smaller and must consume far less memory than a calculated column:
There are obviously scenarios where creating a calculation item for each unique value in a column is not practical but for text categories with not too many unique values, they work quite well.
I hope youve found some useful ideas to explore or this post has sparked some interest in calculation groups. Keep an eye out for dynamic display names for calculation items on the Power BI ideas site and vote!
