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
Hi domtrump not sure what you really need, but another example for running total on link is useful (part RT Sales Customer Class)
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.
- some_bih2 years ago
Community Champion
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