This article is an introduction to Calculation Groups in Power BI in which we will see what they are, how they are developed, and when to use them.
To use Tabular Editor we go to their website to download this application. There are different versions, the commercial (Tabular Editor 3) and the free one (Tabular Editor 2) and choose the one we are most interested in. Once downloaded, in the pbix of the report we are working on, we will have this tool in the External tools tab ready to use it.
In this tutorial, we are working with product sales data and we want to analyze them in different time periods (YTD, MoM and Last Year). But if we want to analyze also the cost and the total quantity, we will have to create 3 measures per KPI, i.e. we have to create 9 measures for this analysis. With the Calculation Groups, we can reduce the number of measurements we have in the report by centralizing the formula in the Calculation Groups.
First, we open Tabular Editor from the report we have open. This connects directly to the tabular model and we can start working.
To create a Calculation Group we have to go to Model -> New Calculation Group. In the right panel, a new element will appear which is the CG. To start creating measurements, we have to deploy it, right-click on the Calculation Items folder, and create a new one.
On the main screen, Expression Editor is where we are going to write the formula we need in DAX.
As mentioned above, we want to calculate YTD, MoM and LY of sales, cost and sales quantity. To only write these 3 measures we have to use the SELECTEDMEASURE() function, since it returns the measure being evaluated, and therefore we get the dynamism we are looking for.
This is how the calculation items would look like:
In addition to creating the three time-based measures, we have added “Actual” to have the measure that returns the value of each KPI without time filters.
Once this development is saved, we go to pbix and click on refresh now button that appears in a yellow strip.
We see that the Calculation Group has been loaded as a table with the following appearance:
To use this element we have to combine the GC field with the previously calculated KPIs (Sales Amount, Sales Cost, and Sales Quantity). For example, we can create a matrix using the name of the calculation group as the row field, and add the 3 measures we have created in the value field, so we see in the same visual the three KPIs analyzed in different time periods.
Also, we can build a slicer with the calculation items to filter the visuals with the type of temporal analysis we want to do. We have built two bar charts, the one above with the KPIs of total sales and total cost per year, and the second one analyzing the total amount of products sold per month and year. If we click on MoM, we see how the Sales quantity by date visual has changed and now shows the monthly growth of the quantity sold.
To conclude, we can say that the Calculation Groups help to optimize the development and maintenance of the necessary measures in a report. In addition to this type of approach, you can create custom groupings, groups of KPIs with different formats (sales vs. margin %), and more.
With creativity, you can create impressive developments!
I hope you enjoyed it.
* Tu información será utilizada exclusivamente para contactarte en relación al Business Data Master. No hacemos spam ni compartimos datos con terceros.