# how to create summary stats with calculation groups.

## introduction.

*Calculation groups *is a useful tool to decrease the number of measures in a data model. Imagine, you have the measures *Quantity*, *Revenue*, *Cost *and *Profit *in place, and you’d like to compare them from a year-to-date perspective. Instead of creating four new measures using the DATESYTD() function, you can create one generic calculation group. This group applies the YTD function onto any measure that you want to see from a year-to-date angle. You can even add other time intelligence members to the calculation group, like month-to-date, year-over-year or previous year. As a result, you get plenty of extra analytical capabilities without even creating one additional measure!

Most probably, time intelligence is the standard use case for calculation groups. However, there are other ones, too! For instance, you can use them to switch between different currencies, units, date dimensions, measures or even formats (see this blog post for some inspiration). Essentially, any time you need to do the same thing over and over again, calculation groups might be a clever and elegant solution. This blog post outlines another scenario, namely using calculation groups to create a bunch of summary stats.

## prerequisites.

- Visual Studio (incl. tabular model with compatibility level 1500 or higher)
- Power BI or Excel

.

**plan of action.**

- Set up summary stats calculation group
- Add calculation items
- Examine calculation group in Excel or Power BI

.

## 1. set up summary stats calculation group.

In Tabular Model Explorer, **right-click **on **Calculation Groups** and **click** on **New calculation group**.

.

**Change **the name of the calculation group to **Summary Stats**.

.

**Right-click **on **Summary Stats **and **add** two columns **Ordinal **and **Summary Calculations** with the following **Properties**:

.

## 2. add calculation items.

**Right-click **on **Calculation Item **and **click **on **New Calculation Item**.

.

**Change **the name and set **Properties **accordingly:

.

**Add** **calculation items **with increasing **Ordinal **number and the following **Expressions**:

**Actual**

SELECTEDMEASURE()

**AVG **

CALCULATE(AVERAGEX([YourTableName], SELECTEDMEASURE()))

**STDEV**

CALCULATE(STDEVX.S([YourTableName], SELECTEDMEASURE()))

**MIN**

CALCULATE(MINX([YourTableName], SELECTEDMEASURE()))

**PERCLOW**

CALCULATE(PERCENTILEX.INC([YourTableName], SELECTEDMEASURE(), 0.25))

**MEDIAN**

CALCULATE(MEDIANX([YourTableName], SELECTEDMEASURE()))

**PERCHIGH**

CALCULATE(PERCENTILEX.INC([YourTableName], SELECTEDMEASURE(), 0.75))

**MAX**

CALCULATE(MAXX([YourTableName], SELECTEDMEASURE()))

SELECTEDMEASURE() is the reference for the measure in use. That is why, we need to use *expression *functions (MAX**X**, MIN**X**, AVERAGE**X** etc.) that first evaluate the measure for each row of a certain table ([YourTableName]) and then apply the summary function. For example, if our measure in use is *Profit *then the calculation group will first compute the profit for every single row in [YourTableName] and afterwards calculate the average, standard deviation, minimum, median and so on.

.

## 3. examine calculation group in Excel or Power BI

To proof that the calculation group is working as expected, I created a few of these measures the old school way (one new measure per calculation) and did a side by side comparison:

.

On the left, one measure per calculation (blue) is displayed- On the right, you can see the actual measure combined with the calculation group (green). This screenshot also illustrates that the approaches demand a slightly different way of assembling pivot tables and graphs.

When dragging calculation groups into a visual, by default, *all *items are displayed. To select only certain calculation members, you can either use a slicer visual or the filters pane.

.

## end.

Now, you could even add other statistical members like variance, modus and skewness or you could play around with some more advanced stuff like distributions, statistical testing or regressions. It’s all on you!

Since you have been reading this rather advanced topic about calculation groups, you’d maybe even interested in other Power BI related topics from tackytech.blog. For instance, how to make a service principal the owner of a Power BI dataset or how to debug tabular model directly in Power BI might make your life easier. For common DAX or Power Query patterns, I recommend checking out how to crack the mystery of the mighty DAX and how to swiftly take over Power Query with the help of some useful gear. There is so much to learn about Power BI. And it is anything else but **tacky**! :P