Forum Discussion
Segment and Cumulative Segment Banding
- 2 years ago
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
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.
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