Stacked calculation groups
If you’re going to carry 5 cups of coffee from the kitchen to the living room, you could walk back and forth 5 times with one cup each time. Some people do. Most people use a tray. Be like most people, when you build your semantic models 😉
Here’s a trick you can use to create a multitude of possible calculations for your report makes or even the end users, without having to create an equal number of measures.
Let’s start of with some data. I made this sample data set with users, a group (department), a date range from 1st of January 2026 to the 14th of September 2026 and finally a random value.
I have a measure that simply summarizes the value:
Now I hade over to the Model view and create a Calculation Group
You’re going to get this popup window. I personally never drag out a value field and aggregate it in a visual, I always use measures, but that’s something I’ve learned over the years and now have as a principle. I’d argue most people don’t.
If the model discourage implicit measures, you can no longer drag a value field to aggregate it. Here’s what that looks like. Note that you can still specifically say “Don’t summarize” to get the same results before, but this is the ONLY option after. Keep this in mind if you intend to add a calculation group to an existing model you’ve already been working on for a while.
Before:
After:
Going back to the calculation group we created, you can add calculation items within the group by right clicking on the group and select New item:
To begin with, I’ve created 2 calculation items that will take whatever measure you throw at it, and show only the last 30 days as well as the last 30-60 days.
Now, I can take ANY measure in the model and split it up over time like this to get the last 30 days and the last 30-60 days. Since my sample model only have 1 measure, it’s not that useful, but I hope you can imagine a model with 25 measures that suddenly became 75 (25 extra for each calculation item gives 50 on top of just using the measures by themselves).
Now for our next step, we’ll create another calculation group! I named this Values and Users. It has 3 Calculation items in it. Value, Users and AVG per User.
The Value is just Selectedmeasure(). The average per user is also quite simple:
To get the median I made this measure with a virtual variable table of users.
In the visual, if I use this calculation group instead, it now looks like this.
Now for a trick 😀 Stack the calculation groups and drill down to next level!
Behold as our SINGLE measure of this model, together with 5 (2+3) calculation items have created 6 new calculated values per department.
Now let’s create a numeric range parameter, because we want to take it to the next level!
I create a slicer from 0-100 with increments of 10 but this is just an example. You can adjust this value to anything when you see what we’re going to use it for below.
Since we now have a Days Value, a measure, that will tell us the number we that the user selected inside the report, we can use it inside a calculation group!
So now I’m going back to my calculation Item for “The last 30 days” but since it’s now going to be dynamic, I rename it to something more generic and change the DAX to fetch the number of days to go back from our Days Value measure.
Now the get the previous period before, I used this calculation. It will always need to be lower than the selected value but higher than the number of days x2. If we still go with 30 like before, it’s therefore less than 30 days ago but greater than 60 days ago. If the user selects 45, the above measure will show 0-45 days and the one below will filter 45-90 days ago. We want comparable periods here.
The new result in the visual looks like this. Now let this sink in. The slicer of days currently have 10 options (10, 20, 30 etc up to 100). The model have 4 fields of data in a single table. It has 1 single measure and 5 departments to group the data by. From this, we’ve been able to create a visual with 30 values (25 + 5 on the totals) x 10 for the days options = 300 calculations.
This is when it starts to blow my mind. The growth where the simplest model possible with only one single measure can explode up into hundreds or even thousands of calculated values now by just dragging and dropping them onto the screen, or even for the user to dynamically decide what they want to see. No running back to the Excel formulas to adjust and recalculate for 3 hours because someone in a meeting wanted to exclude a group or “Yeah that’s 30 day periods but what about 40 days?”.
As a final thought. Imagine a more common model, perhaps you have 100 dimensional values to group by, 15 measures and we now added the tricks you just learned. Those 100 dimensional values already had 100×15 calculations available, so our starting point is 1 500.
Now we’ll add a calculation group with time logic and let’s say 4 different calculation items in it.
We’ll add another that calculates the sum, the average, median and perhaps “number of active users” or something similar, so that’s another 4 items.
We now have our 1 500 X 4 X 4 = 24 000 calculations available. by adding 8 calculation group items.
Work smarter, not harder.
Cheers!
About The Author
