Power BI — calculation groups

Calculation groups are one of the topics that are all over the internet. I can’t count how many posts and tutorials are available. It looks like everyone knows them; they are obvious for developers, and there is nothing more to say about them. But still, during my work with clients, I see some monstrous models containing way too many measures. I won’t write anything new about this topic. This also isn’t a deep dive. Without further ado, let’s start with an example.

The calculation group can be added in the Model view, inside the Calculation groups section.

Rename the Calculation group column. This object will be used in slicers or as columns in visualizations.

Then create calculation items with the following measures.

Current = SELECTEDMEASURE()
PD = CALCULATE(SELECTEDMEASURE(), PREVIOUSDAY(iowa_date[CalendarDate]))
PM = CALCULATE(SELECTEDMEASURE(), PREVIOUSMONTH(iowa_date[CalendarDate]))
PQ = CALCULATE(SELECTEDMEASURE(), PREVIOUSQUARTER(iowa_date[CalendarDate]))
PY = CALCULATE(SELECTEDMEASURE(), PREVIOUSYEAR(iowa_date[CalendarDate]))

We need some arguments for the created measures. SELECTEDMEASURE() will call them during calculations, depending on a context.

AvgOfSales = CALCULATE(SUM(iowa_sale[SaleDollars]))
CountOfSales = CALCULATE(COUNTROWS(iowa_sale))
SumOfSales = CALCULATE(AVERAGE(iowa_sale[SaleDollars]))

OK, everything is ready; it’s time for check them out in practice.

Place the [CalculationType] column into a slicer and into the column pane on the matrix. Then, some other measures you want to calculate. For example, place [SumOfSales] into the values pane and a chosen column from the Date dimension.

Here, the calculation group column on a slicer works like a filter for columns.

We can add more measures to the table.

With one or two period types, it is readable. But, we can go wild and select everything.

We aren’t restricted only to the matrix visual. It look pretty good as well on a chart.

Creating and editing calculation groups weren’t supported by Power BI Desktop for a long time. To avoid this inconvenience, developers were using Tabular Editor. So, we can compare how it looks in TE.

Why calculation groups, you may ask. In this case, we have three measures and five calculation items. With these objects, we can create fifteen combinations of calculations. When we need more combinations, let’s say 10 calculation items and 10 measures. Then, instead of 20 objects, we would need to create 100 measures.