- Ciao friends.
We have been waiting this day for almost two years,
but we finally have calculation groups for Power BI Desktop.
Calculation groups are a useful technique
to modify the behavior of existing measures.
And this could be very useful for problems like
applying time intelligence calculations
to existing measures.
In this example, I have a report without calculation groups.
And if I want to display the Sales Amount,
the value of Sales Amount in the previous year
for the same period in the previous year,
the difference between the current period
and the same period in the previous year in this column,
and the same difference as a percentage,
I need four measures.
Here you see Sales Amount and the other
three variations of this measure.
Now, I need to write this code for Sales Amount,
but what happens if I want to apply
the same transformations to another measure, like Margin?
At this point in order to complete this report,
I should create other variations of the Margin measure.
And I had to create other three measures
with other three DAX expressions,
almost identical for most of the part,
but, this is a time consuming operation
that also pollute my list of measures with a lot of names.
With a calculation group, I have another option.
Let's take a look at the final result I can obtain
using calculation groups.
In this model, I have only four measures.
And you see that I have only Sales Amount,
I don't have all the variations of Sales Amount.
Nevertheless, my report shows the value of
the Sales Amount in a particular quarter or month,
the value of the same period,
of the same period in the previous year,
the difference between these periods,
current period and the same period in the previous year,
and the difference as a percentage.
Now, these transformations of the measure Sales Amount
are obtained through a calculation group
that in the model appears like a table.
In this case, I call this calculation group
Time Intelligence.
And this shows just a column that I named Formula.
In Formula I have four calculation items.
And a calculation item contains a DAX expression
that transforms the value of the existing measure.
In this case, my four calculation items are,
Current, PY, YOY, and YOY as a percentage.
So, this is exactly the same report
I had before for the Sales Amount.
What happens, what I need to do
if I want to obtain the same result for the Margin?
Well, I just have to drag and drop my formula column here
from the Time Intelligence calculation group,
to the report in the comments of my matrix.
And now the same transformations
are applied to the Margin measure.
You can do this thanks to a tool called Tabular Editor.
As you see I have on my ribbon here,
External Tools that has an icon Tabular Editor.
In order to get this icon you have to download
and install on your PC, Tabular Editor.
Tabular Editor register itself
as an external tool for Power BI,
and when you open this tool,
you can access to the tabular model,
which is the model behind the Power BI Desktop model.
You see that there is a warning.
You could create damages,
you could break your model if you don't know what to do,
but actually, when you just add a calculation group,
this is a supported feature.
When you look at the documentation of Tabular Editor,
you will discover that not all of the operation
you can apply over this model,
are supported by Power BI Desktop,
even though this will change and improve over time.
Now, once you are in Tabular Editor,
you see the list of the tables,
the measure that you have in each table,
and the columns you have in each table.
But the calculation group is just like another table.
However, within the calculation group
you see a list of calculation items,
each one with a corresponding DAX expression
that defines the transformation that you want to apply.
In order to create new calculation groups,
of course, you can start here
and create a new calculation group,
but what is more important,
you have to study what calculation groups in DAX are
and what is the right syntax
and the right technique you can use
in your calculation items.
But, once you get the knowledge,
once you understand how to use these techniques,
you can also control other things
that are not possible with the regular measures.
For example, in this report,
you see that they have some calculation group
that is controlling the currency exchange rates
and in supplying the currency conversion
for different currencies,
each one displayed with a proper display format.
So, you can dynamically change
the former string of each measure.
This and much more is possible
thanks to the calculation groups,
and thanks to Tabular Editor
integrated with Power BI Desktop.
So download the Tabular Editor tool,
study the calculation groups,
you can find a lot of content in our website, sqlbi.com.
We also have an entire chapter about calculation groups
in our book, "The Definitive Guide to DAX,"
and of course, we also have a section
in our "Mastering DAX" video course.
So, download Tabular Editor,
install it, study calculation groups.
Enjoy DAX.