topic Re: Segment and Cumulative Segment Banding in DAX Commands and Tips

Segment and Cumulative Segment Banding

domtrump — Sat, 16 Dec 2023 18:51:47 GMT

I use banding all the time to group customers into segments. Very easy. But I also have a need to group those bands in a cumulative matrix such that each band has a cumulative total inclusive of the bands above it. You can see the example below:

How would I write the DAX to produce the matrix on the right from the data on the left?

Re: Segment and Cumulative Segment Banding

some_bih — Sat, 16 Dec 2023 19:28:39 GMT

Hi domtrump not enought data for example so please check link for some ideas

Re: Segment and Cumulative Segment Banding

domtrump — Sat, 16 Dec 2023 20:26:43 GMT

Hmmm. let's try another way just using the basic banding example (I copied this example off the web into my OneNote probably 20+ years ago - it came from this website if it still exists 🙂

<https://exceleratorbi.com.au/banding-in-dax/>

Process for Creating Banding

The better way involves the following process.

Create a table in Excel that contains the group names and the lower/upper age limits
Load the table to Power Pivot
Do not connect the table to any other tables – it is a disconnected table
Write a DAX Calculated Column that compares the age of each customer against the lower/upper limits in your disconnected table and returns the single row from the banding table that matches for each customer.

Here is the detail of how to do it.

Create a Table in Excel

Here is what a table in Excel looks like.

The key features to note are

There is and ID column – this will be used to sort the Band column later
A “Band” column – this is the label that describes each group. Note the groups are mutually exclusive and collectively exhaustive (MECE).
There is a “from” and “to” column that set the lower and upper bounds of each age group. Note that the upper age for one group will match the lower age for the next group. I have used DAX to ensure no overlap in the Calculated Column.

Write a Calculated Column

The objective of the calculated column is to filter the disconnected Banding table so that 1 and only 1 row is “visible” or “unfiltered” for each customer. Here is the formula

= CALCULATE(

VALUES(AgeBands[Band]),

FILTER(AgeBands,

Customers[Age] > AgeBands[From] &&

Customers[Age] <= AgeBands[To]

)

)

The new calculated column could then be placed in a pivot table to get the number of customers in each age grouping. Easy stuff.

What I now need is a pivot table that would use similar grouping but instead of grouping into ranges with a top and bottom value, each segment would have a floor but no ceiling. So I could see
-# customers over 60 (customers in the 60+ band)

-# customers over 50 (customers in the 50-59 band PLUS customers in the 60+ band)

-# customers over 40 (customers in the 40-49 band PLUS customers in the 50-59 band PLUS customers in the 60+ band)
and so on...

Hope that helps clarify. If not, let me know what else would be helpful.

Re: Segment and Cumulative Segment Banding

some_bih — Sat, 16 Dec 2023 23:30:52 GMT

Hi domtrump not sure what you really need, but another example for running total on link is useful (part RT Sales Customer Class)

Re: Segment and Cumulative Segment Banding

domtrump — Sun, 17 Dec 2023 00:34:54 GMT

Not sure what other information I can provide to illustrate? I already have a calculated column that puts the customers into an AGE RANGE (such as age 40-49 or 50-59 or 60+). I can easily produce the counts of customers that are in each of the AGE RANGE segments. I want to create a new calculated column that defines them into a new segmentation based on whether or not they are equal to or above a certain age. (i.e. 50+ would be a segment for any patron over 50. this would include the 50-59 AND the 60+ patrons from the example just gave).
Here's a summary of the data model for this example:

So it is just a matter of how to write the DAX in such a way to create these new "buckets" (or groups or segments or cohorts) at the lower levels would include everyone in the levels above it (i.e. 30+ would include anyone 30-39 as well as 40+ and so on).

I could certaily accomplish this with a long IF statement or SWITCH but I was looking for a more eloquent method using something similar to the banding technique.

Let me know if this sheds further clarity or if there is anything else I can post. The link to running totals looks like it might be useful for time-based calculations but not sure how I would apply it to this scenario. Thanks.

Re: Segment and Cumulative Segment Banding

some_bih — Sun, 17 Dec 2023 08:31:38 GMT

Hi domtrump I will think about it/your case

Re: Segment and Cumulative Segment Banding

some_bih — Mon, 18 Dec 2023 12:34:39 GMT

Hi domtrump

In enclosed file you can find possible solution for Measure Count.

This measure is based on another measures Count 21+ and other, which is based on calculated column Band in Fact table as following
=IF([Age]>=60;"60+";
IF(AND([Age]>=50;[Age]<60);"50+";
IF(AND([Age]>=40;[Age]<50);"40+";
IF(AND([Age]>=21;[Age]<40);"21+";
"21+"))))

Picture for pivot as solution

Picture for Fact table column Band