Dynamic Segmentation with Calculation Groups
One of my favourite things about calculation groups is the ability to use calculation items as filters. In my last blog post I looked at how to leverage this to pass measures as part of the filter context to a tooltip or drill through page. In this post I want to look at how you can use this feature to create dynamic slicers based on calculation groups instead of disconnected table type solutions or static options with calculated columns.
THE SCENARIO:
I recently had a request from a user to add slicer buttons to a report that would dynamically filter and segment some monthly customer counts with the following options:
1. Customers who purchased Category A only and no other categories
2. Customers who purchased Category B only and no other categories
3. Customers who purchased both Category A & Category B
Approaching this scenario by adding a calculated column would only allow a static filter that would not work whithin the filter context of the report which sliced by month. In my main chart I already had two different customer count measures clustered in each month (customers with a single purchase and customers with two or more purchases) and might add more in the future so using a disconnected table to allow the users to switch measures seemed tedious. I needed to dynamically filter the two existing measures in the chart and make it happen on a Friday afternoon.
Luckily calculation groups can filter like a column and calculation items can calculate dynamically like a measure!
THE SOLUTION:
The pattern for the calculation items took the following format:
A table is created for each category that summarizes transactions by customer ID. The customer ID column is then selected from the tables to remove the category. The result of one ID table is then removed from the other and vice versa to get tables of customer IDs with purchases of one category only. The tables are joined with an inner join to get a table of IDs with purchase of both categories. Finally the SELECTEDMEASURE() is filtered by one of the resulting tables. For each of the three calculation items the table used as a filter argument in CALCULATE is changed appropriately to give the different results needed (CategoryA_Only, CategoryB_Only and CategoryAandB)
The resulting calculation group is quite flexible and can be used on different measures for further reporting and as a legend in charts or columns in a table. Its best to stick to simple aggregations when subbing in different measures to evaluate or you can hurt your brain. As is often the beauty of calculation groups, three calculation items can replace a lot of measures if you have some segments that will be reported on frequently. Indeed, using a disconnected table would require a new measure be authored for each different customer count or any other measure I wanted to use. Leveraging calculation groups allows you to create segments that can be continuously reused as part of your model across many different metrics.
Here is another example of a calculation group that segments customers into A, B, C, or D grade according to the SELECTEDMEASURE() evaluated. The segments are calculated using a range from 0 to the highest monthly amount of the SELECTEDMEASURE() for a customer over the last year. The range is segmented into four bins of 25% of the range each (0–25%, 25%-50%, 50%-75%,75%-100%). The customers in the current month of the report are then filtered into the grade segment they fit in to.
Here is an example using the calculation group to evaluate a “Number of orders” measure in each customer segment:
The different solution examples here are not important. What I wanted to point out in this post is the concept that using calculation groups, you can create any dynamic DAX driven table/filter you can imagine and apply it as a slicer in a report (or legend, columns ect). Its worth repeating..…you can use any dynamic filter you can create in DAX in a slicer. For me the possibilities and power of that are tremendously exciting. I’m surprised more people aren’t shouting it from the rooftops. Calculation groups are really the best of both worlds, combining the filter capabilities of a column and the dynamic evaluation of measures. ✌
