Unlocking the Power of Calculation Group - Beyond Time Intelligence

[ajaybabuinturi]

What are Calculation Groups?

Calculation Groups are powerful way to reduce redundancy and simply the data model by enabling reuseable DAX logic that dynamically changes based on context. Let’s move on the practical use cases

Time Intelligence Use Case:

Creation of Calculation Groups, click on Model View (1) >> select Calculation group (2) from the toolbar ribbon. Now you will get a dialogue window having that discourage implicit measures. Please click on Yes (3) to confirm which will ensure that to block adding of data columns/implicit measures to the visuals.

Once you select Yes, or if you already have enabled the discourage implicit measures property, a calculation group is added to your Data-Model. Rename the Calculation group (4) and Calculation group column (5) by doing double-click it in the Data pane or you can use the Properties pane.

The first Calculation item was created with a SELECTEDMEASURE() DAX logic. Rename the Calculation item (6) and edit(7) it as per your business requirement and add additional items if required. 

For additional items right click (8) on the existing Calculated item (YTD) and select New Calculation item (9) and add your logic. In this way you can add additional items. Here I am taking YTD, QTD, MTD metrics for both current and previous years, along with YoY metric for reference.  

Once the additional items (10) have been created, you will notice that the Calculation items (11) are sorted by alphabetical order. If you want to re-arrange them as per your desired order, long-press the desired item and drag it to required position or you can right click (12) on the desired item and select the Move option to re-arrange it accordingly. The Calculation item order (11) will display only when you click on the Calculation items section.

Once you re-order the Calculation items, Reset to default (13) button is enable which will use to reset the order.

Select the Matrix visual and drag the fields as outlined below

Rows: Date Related Columns

Columns: TimeIntelligence group

Values: Required Metric

Here, you will notice that the YoY values are displayed in currency format. To update this to percentage (any other) format, again back to the Model view >> select the YoY (14) calculation item >> enable Dynamic format string (15) to Yes >> Select the Format (16) from drop down, if not populated automatically >> Add your required format as "0.00%" in format bar (17) >> then Commit (18) the changes.

In this way you will change the format for any calculation item as per your requirement.

Now we have seen Time Intelligence use case, let’s explore how this can be useful for Non-Time Intelligence.

Non-Time Intelligence Use Case:

Let’s say you want to compare the performance of two categories across multiple metrics. Traditionally, we would apply a filter for each category and create a separate set of measures for category2. It leads to redundant work and the model is not flexible.

Instead, we can use a Calculation group to dynamically switch between categories, allowing the same base measures to be reused, resulting in a cleaner and more flexible model.

To create New Calculation group, go to Model (1) >> click on the Calculation group (2) >> Select New Calculation group (3) in the Properties pane or right click on the Calculation group and select New Calculation group. I am repeating the same steps for remaining actions.

In this picture you can see both Time Intelligence and Non - Time Intelligence calculation groups.

To create Non - Time Intelligence calculation groups

Rows:

Columns: Non – Time Intelligence group

Values: Required Metrics

This is the final result for Non - Time Intelligence calculation groups and make sure that enable Switch values to Rows.

Calculation groups are not just for Time Intelligence functions they are powerful feature that enhances flexibility and reduces redundancy. Start using them creatively to build smarter and more efficient Power BI reports.

Calculation_Groups.pbix

[Someshn]

Very much helpful 

[Priti_Gaikwad]

@ajaybabuinturi Thanks for sharing 

[Vikranth426]

• Ajay, thanks for this article and its really very usefull

[lokesh0909]

Thank you for sharing this.