Forum Discussion
Helper IISegment and Cumulative Segment Banding
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
Community Champion
Helper IIHmmm. 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.