# Calculation Group for Time Intelligence

Today, I would like to share about the **Calculation Group** functionality in Power BI in my current report.

In the Power BI report, I built the Visualization to show **Sales Revenue** and **Cost Amount** in some measures such as **QTD, YTD, and LYTD.** I created 6 measures: 3 for Sales Revenue and 3 for Cost Amount, so take time.

Then I am using the **Calculation Group** to do it. This function is helping me save time save effort.

{% hint style="info" %}
Calculation groups are a simple way to reduce the number of measures in a model by grouping common measure expressions. Calculation groups work with existing explicit DAX measures by automating repetitive patterns.
{% endhint %}

So, how I can do ... :smirk:

## DAX: `SELECTEDMEASURE`() 

Supporting the Calculation Group function, the new DAX function has been released - [**`SELECTEDMEASURE`**](https://learn.microsoft.com/en-us/dax/selectedmeasure-function-dax)**.**

{% hint style="info" %}
They are used by expressions for calculation items or dynamic format strings to reference the measure that is in context. Returns the measure that is currently being evaluated.

Syntax: **SELECTEDMEASURE()** - without parameter.
{% endhint %}

## Not Using Calculation Group

In my Power BI report, I have the Sales Data with Sales Revenue and Cost Amount. 

I created 2 based measures: **\[Sales Revenue]** and **\[Cost Amount]** for my report.

```dax
-- Based Measure
Sales Revenue = SUM(Sales[Sales Revenue])
Cost Amount = SUM(Sales[Cost Amount])
```

Initially, I have 6 Time Intelligence Measures: 3 for Sales Revenue and 3 for Cost Amount as below.

```dax
YTD Sales Revenue = CALCULATE([Sales Revenue],DATESYTD('Date'[Date]))
YTD Cost Amount = CALCULATE([Cost Amount],DATESYTD('Date'[Date]))

LYTD Sales Revenue = CALCULATE([Sales Revenue],SAMEPERIODLASTYEAR('Date'[Date]))
LYTD Cost Amount = CALCULATE([Cost Amount],SAMEPERIODLASTYEAR('Date'[Date]))

QTD Sales Revenue = CALCULATE([Sales Revenue],DATESQTD('Date'[Date]))
QTD Cost Amount = CALCULATE([Cost Amount],DATESQTD('Date'[Date]))
```

Sample Visualization: **Sales by Region** and **Cost by Region**

* **Sales by Region:** using 3 Time Intelligence measures.<br>

  <figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2Ftv0Ir8Q4jLurOGDcgOa4%2Fimage.png?alt=media&#x26;token=4725cf51-3d30-4b10-afd5-16b191fdcb19" alt=""><figcaption><p>Sales by Region</p></figcaption></figure>
* **Cost by Region:** using 3 Time Intelligence measures.<br>

  <figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FT0d2269B4ca7FYUiOhkh%2Fimage.png?alt=media&#x26;token=d26668c5-faa6-41c2-be26-efcad5dac63e" alt=""><figcaption><p>Cost by Region</p></figcaption></figure>

## Using Calculation Group

Now, I will use this report and using Calculation Group to introduce to you.

The first thing is to make sure you already turned on this feature in ***Option Setting***.

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FelEK76wTydX7oY3gmtXq%2Fimage.png?alt=media&#x26;token=cb991355-84d6-4913-b2a1-2647e262d60a" alt="" width="563"><figcaption><p>Turn on Calculation Group authoring.</p></figcaption></figure>

When using the **Calculation Group** functionality, I will create a Calculation Group called "***Cal-Time Intelligence"*** with corresponding Calculation Items: ***"Item-QTD", "Item-YTD",** and **"Item-LYTD"** for Sales Revenue and Cost Amount.* \
Based on the visualization, the Calculation Group functionality will show related measures for the current measure that is being used.

```dax
 CALCULATION GROUP "Cal-Time Intelligence"
--   CALCULATION ITEMS
        Item-QTD = CALCULATE(SELECTEDMEASURE(), DATESYTD('Date'[Date]))
        Item-YTD = CALCULATE(SELECTEDMEASURE(), DATESQTD('Date'[Date]))
        Item-LYTD = CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR ('Date'[Date]))
    
```

Based on that, open the Power BI report and create...&#x20;

In the **Model View >>** click **Calculation Group.** The new Calculation Group will be created automatically as below.

In the next step, we just create a Calculation Item for each measure **QTD, YTD, and LTYD** using **`SELECTEDMEASURE`**&#x44;AX functionality.

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FD7EkpnrnFgtvQcQ1PBdG%2Fimage.png?alt=media&#x26;token=300fcbdb-dc75-4c89-aede-b97def12bc19" alt=""><figcaption><p>Create Calculation Group</p></figcaption></figure>

Create **Calculation Items**:

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FVM0nZ2Wg3PR6SZYEBOW6%2Fimage.png?alt=media&#x26;token=f2afdcaa-e53b-46f1-898c-00e658fb7a2b" alt=""><figcaption><p>Calculation Group &#x26; Items</p></figcaption></figure>

That's here, I did finish creating Calculation Items.&#x20;

Now I will show you how this function is running... :chart:

## Checking now ...

Back to my report, I used the Matrix Table visualization

**Sales by Region (Matrix):**&#x20;

* Column: using Calculation Group Column "***Time Intelligence"***
* Value: using based measure "***Sales Revenue"***

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FajrCqP4GyaPf8BYuYzno%2Fimage.png?alt=media&#x26;token=d4696e85-16d3-4c40-9a92-1d284949c875" alt=""><figcaption><p>Sales by Region (Matrix): using Calculation Group and Base Measure "Sales Revenue"</p></figcaption></figure>

You can see, the showing column which is ***Calculation Items** -* you don't need to drag & drop many measures for this visualization as initially. When using Calculation Group, the Power BI engine will calculate and show these Calculation Item columns automatically.

**Cost by Region (Matrix):**&#x20;

* Column: using Calculation Group Column "***Time Intelligence"***
* Value: using based measure "***Cost  Amount"***

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FEQpsvlDrCKnlz6kicB8X%2Fimage.png?alt=media&#x26;token=8f81319f-31e5-4d6d-9e6d-746880d8ad04" alt=""><figcaption><p>Cost by Region: using Calculation Group and Base Measure "Cost Amount"</p></figcaption></figure>

For each visualization, we will show another insight by another measure. So, the **Calculation Group** with Dax **`SELECTEDMEASURE`** that helps bulk calculate and show measures is in context. Moreover, it's helped me reduce many measures I need to create and maintain for my report.

<figure><img src="https://4233060750-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjhtQupP7ACZVtv3cCNCr%2Fuploads%2FNfU6eq43r0i7JSynCfbZ%2F_Calculation_group.gif?alt=media&#x26;token=77805849-d07f-400b-845f-f5bae7a5c624" alt=""><figcaption><p>Calculation Group sample: Time Intelligence</p></figcaption></figure>

Hoping well... :apple:

**\[NTD]yns.asia**\ <mark style="color:red;">...</mark>[<mark style="color:red;">invite me a cup.</mark>](https://ko-fi.com/ntdyns/?ref=qr\&amp;v=2) :coffee: Thank you. :heart: