Combining calculation groups in Power BI to create powerful reports
I recently posted on Twitter about combining calculation groups to create a tooltip that works for any measure value in a matrix. The idea seemed to get a bit of love from the Power BI community so I decided to write up an explanation of how to achieve it and some of the concepts behind it.
THE SCENARIO:
The business requirement for the report was to be able to drill into and explore issues with margin. The example from the report is a drill through landing page that shows a breakdown of different costs across different products and distribution channels. This is the last area in the users journey into the numbers through the report and, being restricted by a number of filters from the main report pages, it needed to provide a lot of detail, breaking the margin down into a lot of component costs.
I had created the matrix with a number of different measures for the columns and could instantly see that it was difficult to tell wether a cost percentage was high or low as there was no context. There was already a lot of information in the matrix so I figured that a tooltip showing how a given matrix value compared to previous months would be a great way to give users some extra context around the cost percentages. I wanted to pull out the same Maximum, Minimum and Average values over the past 6 months for each number and I was dealing with measures so calculation groups was the logical place to start.
THE SOLUTION:
I started by creating a simple calculation group in Tabular Editor with the three calculation items I needed for applying a monthly Min, Max and Avg. These used the following format, just changing the iterating function appropriately for each measure:
I added one calculation item for the current value which is just SELECTEDMEASURE()
These three calculation items were used as the X axis for a bar chart in a tooltip with a random placeholder measure as the chart value.
I applied the tooltip to my matrix and hovered over a percentage value. No dice! Only the random measure I had used as a placeholder in the tootip chart value field showed. This is because a tooltip picks up the filter context of the data point you hover on and applies it to the visuals and measures it contains. It doesnt pick up the actual measure that is being evaluated. My calculation group was setup to transform a measure and no measures where available for it to transform, just a set of filters.
Calculation groups act as a column in your model though (a column where each row can be a different measure or DAX expression) and columns can be used to filter. With this in mind, I rebuilt the report matrix using a calculation group with each measure from my original matrix as the calculation items.
Because the measures are now column values in the model, they become part of the filter context and the measure gets passed to the tooltip just like other filters. You do need to make sure that the precedence is set correctly and that the precedence value for the tooltip calculation group is lower than the one for the calculation group used for the matrix columns. This ensures that the matrix values are evaluated first and tooltip second.
I deployed the report and posted my idea on twitter. The clouds parted and my ego smuggly basked on a cabana chair sipping a pina colada. I positioned my hands and backside in readiness for the flurry of high fives and back slapping that was surely headed my way from the users.
“ I love the report but can you build me a new one where I can see dollar amounts instead of the percentages?” Was the first bit of feedback I received. 🤦♂️
The user was absolutely right. In the past, I would have duplicated the table and switched out the percentages for dollar amounts then used some buttons and bookmarks to switch between the table with percentages and the table with dollars (still a decent solution). Now with calculation groups and custom format strings though, this was not necessary. I created a disconnected table (‘DollarsPercentage’) with two rows, “$” and “%”. I then returned to the calculation group containing my cost percentage measures and added a simple IF statement to each calculation item to change the percentage measures based on the value of the disconnected table.
Because the measures change between percentages and dollars we can use custom format strings and another simple IF statement in the calculation items format string expression to ensure the format displays correctly.
And there you have it, a real world scenario where combining two relatively simple calculation groups can quickly create a powerful report that, dare I say, would not be possible otherwise.
The key takeaway here is the ability to use calculation groups to insert measures into the filter context and leveraging this with power BI report features. A different example of the concept would be utilising a drill through page. You could, for example, use a calculation group containing a list of measures in a chart and add the calculation group as a drill through field on a new page. You can then apply other filters to the chart as you wish and drill through on a measure item to the new page passing the filters and the specific measure.
I hope this concept gives you some inspiration for using calculation groups in your own reports.
Cheers
Kane
