Calculation Groups with Tabular Editor

In this article, let’s look at a powerful technique – calculation group. It’s powerful, but surprisingly simple. It just needs two things, an external tool Tabular Editor and your courage to give it a try.

We’re using AdventureWorks dataset for demonstration.

Requirement:

1. Calculate Total Sales, Total Cost and Total Margin.
2. Display these calculations by country names and by months.
3. Look at the above numbers in 4 time periods: MTD, QTD, YTD, and TTM.

This requirement is often seen in the real world.

Issues before using Calculation Groups:

Let’s create 3 base measures using the following DAX codes for the first requirement:

Total Sales = SUM( Sales[Total Sales] )
Total Cost = SUMX( Sales, Sales[Product Standard Cost] + Sales[Tax Amt] + Sales[Freight] )
Total Margin = [Total Sales] - [Total Cost]

Then, for the second requirement, we put these measures in a visual, for example, a table, and drag the calendar hierarchy into a slicer visual:

Now it’s time for the third requirement. Let’s create 3 MTD measures on top of the 3 base measures:

Sales MTD = CALCULATE ( [Total Sales], DATESMTD ( 'Date Table'[Date] ) )
Cost MTD = CALCULATE ( [Total Cost], DATESMTD ( 'Date Table'[Date] ) )
Margin MTD = CALCULATE ( [Total Margin], DATESMTD ( 'Date Table'[Date] ) )

This works. Then we need to continue with QTD, YTD and TTM with the similar method, only changing the DATESMTD ( 'Date Table'[Date] ) to the follows:

For QTD: DATESQTD ( 'Date Table'[Date] )

For YTD: DATESYTD ( 'Date Table'[Date] )

For TTM: DATESINPERIOD ( 'Date Table'[Date], MAX ( 'Date Table'[Date] ), -1, YEAR )

There are 3 measures for each time period. So we end up with 15 measures in total, including the base measures.

Imaging we have more basic measures for different metrics that a business likes to look at, the number of measures will grow fast. Most of them are redundant regarding techniques.

Solution with Calculation Groups:

This is when Calculation Groups with Tabular Editor comes to the play.

The idea of Calculation Groups is to add another layer of calculation on a measure. In this example, you only need the 3 base measures as the first layer of calculation, and then create the Calculation Items for the second one. Sounds confusing? Let’s look at an example of how to use Calculation Groups to modify the above DAX solution.

First, install Tabular Editor on your computer. It will automatically install into Power BI Desktop. You’ll find it in the External Tool.

In Table Model, right click and select Create New -> Calculation Group. Give it a name “Time Period Group”.

Right click it and select Create New -> Calculation Item. Give it a name “All Time”. Then each time of creating a new calculation item, right click the “Calculation Item”.

The codes for most of the often used time period items are:

All Time = SELECTEDMEASURE ()

Rolling 7 Days =
VAR Result = 
    CALCULATE ( 
    SELECTEDMEASURE (), 
    DATESBETWEEN ( 'Date Table'[Date], 
    MAX ( 'Date Table'[Date] ) - 7, 
    MAX ( 'Date Table'[Date] ) - 1 ) ) 
RETURN 
Result + 0

MTD = CALCULATE ( SELECTEDMEASURE (), DATESMTD ( 'Date Table'[Date] ) ) + 0
QTD = CALCULATE ( SELECTEDMEASURE (), DATESQTD ( 'Date Table'[Date] ) ) + 0
YTD = CALCULATE ( SELECTEDMEASURE (), DATESYTD ( 'Date Table'[Date] ) ) + 0
TTM = 
    CALCULATE (    
    SELECTEDMEASURE (),    
    DATESINPERIOD ( 'Date Table'[Date], MAX ( 'Date Table'[Date] ), -1, YEAR ) ) + 0
WOW =
VAR Rolling7Days =    
    CALCULATE (        
    SELECTEDMEASURE (),        
    DATESBETWEEN (            
        'Date Table'[Date],            
        MAX ( 'Date Table'[Date] ) - 7,            
        MAX ( 'Date Table'[Date] ) - 1 ) )
VAR Rolling8to14Days =    
    CALCULATE (        
    SELECTEDMEASURE (),        
    DATESBETWEEN (            
        'Date Table'[Date],            
        MAX ( 'Date Table'[Date] ) - 14,            
        MAX ( 'Date Table'[Date] ) - 8 ))
VAR Result = Rolling8to14Days - Rolling7Days
RETURN    
Result + 0
MoM = 
VAR _currentMTD =    
    CALCULATE ( SELECTEDMEASURE (), DATESMTD ( 'Date Table'[Date] ) )
VAR _priorMTD =    
    CALCULATE ( SELECTEDMEASURE (), DATEADD ( 'Date Table'[Date], - 1, MONTH ) )
VAR Result = _currentMTD - _priorMTD
RETURN    
Result + 0
MoM% =
VAR _currentMonth = 
    SELECTEDMEASURE ()
VAR _priorMonth =    
    CALCULATE ( SELECTEDMEASURE (), PARALLELPERIOD ( 'Date Table'[Date], - 1, MONTH ))
VAR _MoM = _currentMonth - _priorMonth
VAR Result = DIVIDE ( _MoM, _priorMonth )
RETURN    
Result + 0
QoQ = 
VAR _currentQTD =    
    CALCULATE ( SELECTEDMEASURE (), DATESQTD ( 'Date Table'[Date] ) )
VAR _priorQTD =    CALCULATE ( SELECTEDMEASURE (), DATEADD ( 'Date Table'[Date], - 1, QUARTER ) )
VAR Result = _currentQTD - _priorQTD 
RETURN    Result + 0
QoQ% =
VAR _currentQTD =    
    CALCULATE ( SELECTEDMEASURE (), DATESQTD ( 'Date Table'[Date] ) )
VAR _priorQTD =    
    CALCULATE ( SELECTEDMEASURE (), DATEADD ( 'Date Table'[Date], - 1, QUARTER ) )
VAR _value = _currentQTD - _priorQTD
VAR Result =  DIVIDE ( _value, _priorQTD )
RETURN    
Result + 0
YoY = 
VAR _currentYear =     
    SELECTEDMEASURE()
VAR _previousYear =     
    CALCULATE ( SELECTEDMEASURE(), SAMEPERIODLASTYEAR( 'Date Table'[Date] ) ) 
VAR Result = _currentYear - _previousYear
RETURN    
Result + 0
YoY% =
VAR _currentYear = SELECTEDMEASURE()
VAR _previousYear =     
    CALCULATE ( SELECTEDMEASURE(), SAMEPERIODLASTYEAR( 'Date Table'[Date] ) )
VAR _YoY = _currentYear - _previousYear
VAR _result = DIVIDE (_YoY, _previousYear
RETURN    
Result + 0 
PY = 
    CALCULATE (    
    SELECTEDMEASURE (),    
    SAMEPERIODLASTYEAR ( 'Date Table'[Date] ) ) + 0

Applications:

There are three ways to use the Calculation Groups in a report.

1.  Use it as a slicer.

2.  Use it in a calculated measure.

Avg. Orders per Person YoY =

CALCULATE(

[Avg. Orders per Person],

'Time Period Group'[Name] = "YoY"

)

3.  Use it in a visual such as a card.