Calculation Group for Time Intelligence

#PowerBI, #Measure

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.

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.

So, how I can do ... 😏

DAX: SELECTEDMEASURE()

Supporting the Calculation Group function, the new DAX function has been released - SELECTEDMEASURE.

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.

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.

-- 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.

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.

  

  Sales by Region
• Cost by Region: using 3 Time Intelligence measures.

  

  Cost by Region

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.

Turn on Calculation Group authoring.

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.

 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...

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 SELECTEDMEASUREDAX functionality.

Create Calculation Group

Create Calculation Items:

Calculation Group & Items

That's here, I did finish creating Calculation Items.

Now I will show you how this function is running... 💹

Checking now ...

Back to my report, I used the Matrix Table visualization

Sales by Region (Matrix):

• Column: using Calculation Group Column "Time Intelligence"

• Value: using based measure "Sales Revenue"

Sales by Region (Matrix): using Calculation Group and Base Measure "Sales Revenue"

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):

• Column: using Calculation Group Column "Time Intelligence"

• Value: using based measure "Cost Amount"

Cost by Region: using Calculation Group and Base Measure "Cost Amount"

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.

Calculation Group sample: Time Intelligence

Hoping well... 🍎

[NTD]yns.asia ...invite me a cup. ☕ Thank you. ❤️