Sometimes in the course of Power BI report development, we’ll find ourselves writing a lot of measures. In some of these occasions we may find that a group of measures all follow a particular pattern. Similarly, we may also find that the report pages themselves are repeatable and allude to a more streamlined solution.

While working on a recent project, I found myself asking these very questions, and felt that the issues could be solved with Calculation Groups. Turns out – they can be – so I want to share two examples of how the solutions were applied.

First let’s look at the data.

Ok – the numbers here aren’t really that important, but what is important is that the two desired result columns don’t natively produce total values.

This may not seem like a big problem (since we have a result for any row with a date context), but it clearly becomes an issue when we try to use these values in certain kinds of visuals like cards, gauges, or clustered column charts.

The results of these visuals are empty – because they natively represent the TOTAL row, which in the case of our desired measures is also blank.

We can overcome this limitation by writing some DAX that gives us the ability to calculate the total. This requires a virtual summary table with our target measure thrown in. Then we filter that virtual table based on our requirements and sum the column containing our target measure.

And with our new calculation, our target measure now produces a total. When we substitute this measure into our visuals, they all populate with the correct values.

We can follow the same pattern with our summary table formula and produce a similar measure that returns the Average Per Day (not pictured). Then we simply rinse and repeat these two measures for our other target result: Earned Revenue.

Here’s where a pattern starts to stand out. The formulas used between Billed and Earned Revenue with Totals are EXACTLY the same – except for the [measure] we add to the summary table column. This means we are writing a lot of measures that look and feel almost identical.

So if you’re keeping count – we have created 4 distinct target measures with 2 repeatable patterns.

  1. Billed Revenue with Totals
  2. Earned Revenue with Totals
  3. Billed Revenue Average Per Day
  4. Earned Revenue Averageper Day

When we start building a report with these measures – we might opt to build a report page for Billed Revenue and another one for Earned Revenue. The pages again are largely identical except for the measures used in each visual.

This isn’t too bad, but imagine if there are 10 other desired measures that all follow the same patterns. Are we going to add 10 more report pages and create 20 new measures? Overtime, this kind of solution would become unwieldy and hard to manage. For example, what happens if we need to tweak one of our identical measures? We’d have to change it 12 times!

Use Calculation Groups to reduce the number of measures and pages.

We’ve walked through the setup. Now let’s talk about the solution. Imagine building 1 report page that contains a slicer that allows the user to select the desired measure of choice. Then all the visuals would update to that target measure. This would reduce our report page count to just 1.

Likewise – if we use “SELECTEDMEASURE() – in our TOTAL or AVERAGE BY DAY measure instead of writing a new formula each time – we can reduce the number of total measures we need for this solution.

This can all be accomplished with calculation groups! Let’s walk through this process: Our first step is to modularize the Total and Average per Day formulas so they can be reused.

Reusing Measures with a Calculation Group

These next steps require Tabular Editor – which can still be obtained for free (if using v2).

At this point – the calculation group produces a 1 or 2 column table – not a formula.

To use these in our report, we have to build visuals that incorporate information from our Calculation Group table. This is commonly demonstrated using a matrix visual, where our calculation item column (titled Name) is used in the columns field and our target measure for the values.

In our case – we aren’t using any matrix tables in our report – so how can we reuse this pattern for a card or clustered column chart? We simply need to add the calculation item column and target measure just like in the matrix table.

Now of course – we can see both Average and Total in the chart above – so to only show the target measure – we can edit the visual level filter.

Perfect! Now rinse and repeat this pattern with the Card Visual elements as well.

Perfect – we can effectively recreate our report page using this same strategy. And if we want to show a different target measure – we just have to swap it out in the Field well.

Use Calculation Groups to create a Measure-Slicer

So now that we’ve tuned each visual to accept a calculation group item – here’s how we can swap out the formulas on the page with a slicer.

Let’s walk through this setup:

This will then create a second calculation group table with our calculation items (measure names) in a column

At this point our slicer will now drive the changing of all the measures.

But look closer at the gif above. Although our measures are changing with the slicer – the name of the measure in the charts (particularly the card visual) remain unchanged.

The truth is – we could have put any measure in the visual to start with – because our 2nd calculation group slicer overwrites this measure with the one selected in our slicer.

For illustrative purposes, let’s create a ‘holding measure’ that we will place in the card visual instead.

So the numbers are correct – but the ‘name’ is not. Perhaps we can write some DAX against the calculation group measure slicer to extract the name of the measure selected – and place it as a title on this card (hiding the category name).

Unfortunately, we cannot write formulas against the calculation group column. Formulas like Selectedvalue() that would normally act on a slicer selection – do not return any values when directed at our calculation group slicer.

The work-around here is to NOT use a measure. Instead – just use another iteration of the calculation group name column – and change it to a card. This allows us to have the measure name on the top of the page to change with the slicer selection.

Unfortunately – since this is not DAX created, we cannot use this item in the Titles of our visuals. Instead we have to hard-code the visual titles and hide the ‘holding measure’ name which does not change.

With just a few steps we were able to reduce the number of measures and report pages in a report to a very manageable and appropriate number. While this strategy may not apply in all circumstances, it’s a helpful trick to have handy when this kind of pattern arises.