If you would like to go along with this tutorial, please make sure you have downloaded the following:
- Power BI: Download here.
- Tabular Editor: Download here.
- SQL Server 2019 or Azure Analysis Services or power bi with external tool. (July 20 or later)
Alternative Calculation Group Approach
I’m not going to repeat all the technical details that Microsoft has already nicely written here.
The goal of this post is to show another approach on how to use the calculation groups and demonstrate why you may prefer to use this alternative approach instead of the one suggested in the Microsoft article.
First let’s weigh the Pros and Cons of the Calculation Groups
As Microsoft says, Calculation groups address an issue in complex models where there can be a proliferation of redundant measures using the same calculations – such as Sales PY, Cost PY, Profit PY, Sales YTD, Profit YTD and so on.
So the main benefit of using Calculation Groups is to reduce the number of measures to create and to maintain.
If we have 20 measures in our model and each one of them has 5 time intelligence calculations we end up having to create and to maintain 100 separate measures.
However, one of the disadvantages of using Calculation Groups is that it is not flexible.
Users have to drag in column the Time Intelligence and use the Time Calculations slicer to select the time calculations they want to see on their dashboards.
So building reports, dashboards or pivot table becomes very limited in term of displaying, sorting and formating the measures as we wish.
For example, many users want to see a report with Cost, Cost PY, Sales, Sales PY, Profit, Profit PY and Profit YOY. Truth is that now we cannot achieve that with the calculation groups. (Unless we use the below alternative method)
So let’s create a PBI matrix using the Calculation groups feature.
What’s wrong with it?
- We cannot order the measures as we wish
- We cannot show the YOY profit only without showing cost and sales
- We cannot apply conditional formatting only on YOY
- Users have to drag item calculation in column might be confusing
- Many users especially in finance, prefer to use the pivot table in Excel instead of PBI so calculation groups become even less flexible
Hold on, is calculation group that bad?
Of course not in many scenarios using the recommended Calculation Groups approach will be perfectly fine. And even with the different approach that I’m going to show Calculation group is still a real game-changer.
The way I like to use Calculation Groups doesn’t help to reduce the number of measures but it still drastically speeds up the time of creating 100 measure and reduces the burden to maintain them.
Here is the visualisation I need to have:
– YOY for profit only
– Conditional formating on profit
– Custom order
We cannot build such a matrix using the recommended approach of Calculation Group.
However, using the alternative method I recommend we can achieve it while still taking advantage of the calculation group capability to speed up our development and ease any future changes.
Create our Calculation Items
So here is how we can create a PY Time Intelligence using calculation groups. All the technical details and different patterns are available here
Generate Time Intelligence Measure
As said above we need to create separate measures, however, as we use the calculation groups feature it helps to speed up the development time as we no longer need to copy/paste long the time intelligence formulas.
Also maintaining existing measures becomes very easy as whenever we need to change the logic of a formula we’ll make the change in only one place.
Additionally, all the separate measures automatically inherit the format properties of the calculation item so we can maintain the format in only on place as well.
Sales PY = CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "PY" )
Generate Multiple Measures
Using the Advanced Script of Tabular Editor we can create multiple measures at once and thus speeding up the development time even more.
The code below creates a custom action which generates PY measures for every selected measures.
// Creates a PY measure for every selected measure.
foreach(var m in Selected.Measures) {
m.Table.AddMeasure(
m.Name + " PY", // Name
"Calculate(" + m.DaxObjectName + ", 'Time Intelligence'[Time Calculation]="PY")",
m.DisplayFolder // Display Folder
);
}
Once the code compiles we can save the script and name it accordingly.
Now we just need to select the measures for which we want to generate PY time intelligence measure.
Below I generate six measure in only two clicks. Now imagine if we have 50 measures to generate, it would still take us only two clicks!
Reuse Custom Actions
Another great thing is that we can even reuse our custom action in any models. No need to recode or copy/paste our script, all we need to do is to import our existing custom action:
Custom Actions are stored in the CustomActions.json file within %AppData%LocalTabularEditor.
By combining the Calculation Groups feature with the Advanced Scripting of tabular editor we not only take the full advantage of the calculation groups but we also keep the flexibility of having separate measures.
Key Takeaways:
- Drastically reduce time to develop our model
- Take the full advantage of the calculation groups such as dynamic formatting, dynamic currency conversion…
- Keep all the flexibility of having separate measures
- If we need to change the logic of our measures we change it on only one place: the calculation item
You can find more information about the Advanced Scripting here.
I hope you found this post useful and please let me know if you like or don’t like this method.
