Sounds confusing? Let’s look at an example of how to use Calculation Groups to modify the above DAX solution.įirst, install Tabular Editor on your computer. 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. The idea of Calculation Groups is to add another layer of calculation on a measure. This is when Calculation Groups with Tabular Editor comes to the play.
Most of them are redundant regarding techniques. Imaging we have more basic measures for different metrics that a business likes to look at, the number of measures will grow fast. So we end up with 15 measures in total, including the base measures. There are 3 measures for each time period. Then we need to continue with QTD, YTD and TTM with the similar method, only changing the DATESMTD ( 'Date Table' ) to the follows: For QTD: DATESQTD ( 'Date Table' ) For YTD: DATESYTD ( 'Date Table' ) For TTM: DATESINPERIOD ( 'Date Table', MAX ( 'Date Table' ), -1, YEAR ) Let’s create 3 MTD measures on top of the 3 base measures: Sales MTD = CALCULATE (, DATESMTD ( 'Date Table' ) ) Cost MTD = CALCULATE (, DATESMTD ( 'Date Table' ) ) Margin MTD = CALCULATE (, DATESMTD ( 'Date Table' ) ) This works. 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: Total Cost = SUMX( Sales, Sales + Sales + Sales ) Let’s create 3 base measures using the following DAX codes for the first requirement: Total Sales = SUM( Sales )
This requirement is often seen in the real world. Look at the above numbers in 4 time periods: MTD, QTD, YTD, and TTM.Display these calculations by country names and by months.Calculate Total Sales, Total Cost and Total Margin.We’re using AdventureWorks dataset for demonstration. It just needs two things, an external tool Tabular Editor and your courage to give it a try. In this article, let’s look at a powerful technique – calculation group.
0 kommentar(er)
