Calculation Groups-An effective way to reduce measures in PowerBI

Learn the concept of Calculation Groups in Power BI

Image by Sarah Pflug from Burst

Microsoft in its Power BI July, 2020 release, introduced the ability to launch External Tools from Power BI desktop (currently a preview feature). With the help of this feature, Tabular Editor can now be integrated with Power BI and allows users to easily create Calculation Groups in Power BI.

What is the benefit of Calculation Groups

We all know the pain and effort that goes in creating measures that use same calculation for calculating the value of different parameters; particularly Time intelligence measures.

For e.g., to calculate the value of Month to Date(MTD), Quarter to Date(QTD), Year to Date(YTD), Previous Year (PY) for two fields -Sale Value and Order Quantity, it requires you to create a separate measure for each of these calculations, which will be equivalent to 8 measures. Just imagine, if Time intelligence calculation is also to be done for two more fields; such as, Returned Item Value and Returned Item quantity; the total measures to be created in Power BI would be 16!! This goes on increasing with each field/parameter.

The story does not end here!! The user then needs to select and individually use the created measures in the visuals. This seems like a futile exercise of creating multiple measures that use same calculation.

Calculation Group can act as a saviour in such situations. It eliminates the need to create a seperate measure for each calculation and reduces the total number of measures created in PowerBI. Lets cut to the chase and directly go to the process here!!

How Calculation Group Works and the process to create Calculation Group

For creating a Calculation Group, the following steps need to be followed:

1. Download and Install Tabular Editor from www. tabulareditor.com
2. In PowerBI Desktop Preview Features, Enable “Store dataset using enhance metadataformat”.
3. In PowerBI Desktop, after sourcing the data and creating the data model, click on ‘External Tools’ Tab.

Image1: Tabular Editor Feature accessible from External Tools Tab

3. Clicking on the Tabular Editor , launches the Tabular Editor with the Tables available in the model. From the Tabular Editor, a Caluclation group can be created by Right Clicking on Tables>Create New>Calculation Group

4. Calculation Group can be considered like a folder/table that stores multiple measures which are known as Calculation Items. Here, I have created a new Calculation group — ‘Time Intelligence Measures’. Right Click the Calculation Group and Create a Calculation Item — MTD(Month To Date). The screenshot below shows the Calculation item -MTD with the DAX statement:

MTD → CALCULATE(SELECTEDMEASURE(),DATESMTD(‘Date’[Date]))

Note: Instead of using the field value/parameter (such as Sales Amount, Order Quantity) for which the MTD value is to be calculated, use the function SELECTEDMEASURE()

Image 2: Create Calculation Item (MTD)

5. In the same way, multiple Calculation Items; such as, QTD(Quarter To Date), YTD(Year to Date), PY(Previous Year Value) can be created using the following DAX statements:

QTD → CALCULATE(SELECTEDMEASURE(),DATESQTD(‘Date’[Date]))

YTD → CALCULATE(SELECTEDMEASURE(),DATESYTD(‘Date’[Date]))

PY → CALCULATE(SELECTEDMEASURE(),SAMEPERIODLASTYEAR(‘Date’[Date]))

6. Save the Calculation Group by click on File>Save in the Tabular Editor and go to PowerBI Desktop. The created Calculation Group will appear in the PowerBI Desktop Fields section as a Table with only one field/column- ‘Name’.

7. Now here comes the real power of PowerBI into action! To see the value of order quantity MTD, QTD,YTD and Last Year, select a Matrix Visualization and drag the fields/measures as shown in the screenshot below:

Image 3: Matrix Visualisation showing the time intelligence value for order quantity

*In the image above Total Qty = SUM(Sales_Data[Order Quantity]).

8. Please note that Calculation Group only works with Explicit DAX measures and do not work with implicit DAX measures. For example, in Power BI implicit measures are created when a user drags columns onto visuals to view aggregated values, without creating an explicit measure. Therefore, I have created the measure ‘Total Qty’ here.

9. When the user drags the Name item from the Calculation Group to the Columns area, each Calculation item(MTD,QTD,YTD, Last Year) which was created in the Tabular Editor is shown as a separate column.

10. In the same way, if the time intelligence function values are also to be displayed for another field in the report, say -Sales Amount, there is no need to create separate measures for that. Just create one explicit measure — ‘Total Sales’ = Sum(Sales) and add it to the Values section in your matrix and let Power BI do the rest.

Conclusion:

With the help of this newly introduced feature, users can now save huge amount of time that goes in creating and maintaining measures in their models. Now, with this feature just create the base measures in PowerBI and use Calculation Group to create the time intelligence representations of all the base measures in a quick and efficient way!!