REDUCE Your Measures With Calculation Groups In Power BI

 

You will need an external tool Tabular Editor for this. If you already don't have it, then please install it first.

You can visit the Tabular Editor website to download the latest installer.

Calculation groups are a new feature in DAX, inspired by a similar feature available in MDX known as calculated members. Calculation groups are easy to use; however, designing a model with calculation groups correctly can be challenging when you create multiple calculation groups or when you use calculation items in measures.

Let's see first why calculation groups are useful:

Imagine we have these measures:

Sales Amount   := SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

Total Cost     := SUMX ( Sales, Sales[Quantity] * Sales[Unit Cost] )

Margin         := [Sales Amount] - [Total Cost]

Sales Quantity := SUM ( Sales[Quantity] )

And we want to calculate YTD, MTd ...etc for these measures:

YTD Sales Amount :=

CALCULATE (

    [Sales Amount],

    DATESYTD ( 'Date'[Date] )

)

 

YTD Total Cost :=

CALCULATE (

    [Total Cost],

    DATESYTD ( 'Date'[Date] )

)

 

YTD Margin :=

CALCULATE (

    [Margin],

    DATESYTD ( 'Date'[Date] )

)

 

YTD Sales Quantity :=

CALCULATE (

    [Sales Quantity],

    DATESYTD ( 'Date'[Date] )

)

Instead of creating all these measures, we can simply create a calculation item that takes a measure and calculate its YTD, and hence we can create a calculation group of all desired calculation items.

First: Launch tabular editor from your model 

Then Create a new calculation group

I named the group Time Intelligence and created a couple of 
calculation items

And it goes like this:

every calculation item takes the selected measure and applies the desired filter to it

-

-- Calculation Item: YTD

--

    CALCULATE (

        SELECTEDMEASURE (),

        DATESYTD ( 'Date'[Date] )

    )

 

--

-- Calculation Item: MTD

--

    CALCULATE (

        SELECTEDMEASURE (),

        DATESMTD ( 'Date'[Date] )

    )

So, I've many measures for calculating certain values, so basically after selecting all the measures
I dragged and dropped "Name" which is my calculation group in columns
so what you see here is MTD, YTD for all measures To the selected date 
in the slicer.

Also, You can use your calculation group in a slicer of course.

Be careful: calculation groups turn off your implicit measures and you will have to write all of them explicitly.

Conclusions

Calculation groups are a new and exciting feature of DAX. In this first article, we just introduced calculation groups. The following articles describe in more detail the properties available, several examples of possible usages, and the best practice to write reliable code.