Forum Discussion
How to Make segments
Hi all, I am stuck at some dyanmic segmentation part.
Screenshot :
I am trying mark four segments(A,B,C,D) for above users bit dynamically either via path-X or Path-Y whichever can work for larger dataset.
In above sample data , there are 228 users(MUID) where the each user have some frequency and the sum(frequency) for all users is 500.
Path-X : 500/4 = 125, how can I extract the users or mark the segment-A till the sum reaches the first 125, then seg-B for 125 to 250 ,so on.
Path -Y: Instead of frequency, can we use the %GT of frequency.
Note: columns are sorted by frequency desc.
Sample data is in data.csv here
Hi, Anonymous
According to your description, you want to add dyanmic segmentation based on the accumulate sum value, you can try my method:
- Go to power query editor to add an index column:
- Create two measures:
Accumulate sum = CALCULATE( SUM(data[Col_Frequency]), FILTER(ALLSELECTED(data), [Index]<=MAX([Index])))dyanmic segmentation = var _sum=SUMX(ALL(data),[Col_Frequency]) var _segment= SWITCH( TRUE(), [Cummulative sum]<=0.25*_sum,"A", [Cummulative sum]<=0.5*_sum,"B", [Cummulative sum]<=0.75*_sum,"C", [Cummulative sum]<=_sum,"D", BLANK()) return _segment- Place the [dyanmic segmentation] into the table chart, and you can get what you want, like this:
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi, Anonymous
According to your description, you want to add dyanmic segmentation based on the accumulate sum value, you can try my method:
- Go to power query editor to add an index column:
- Create two measures:
Accumulate sum = CALCULATE( SUM(data[Col_Frequency]), FILTER(ALLSELECTED(data), [Index]<=MAX([Index])))dyanmic segmentation = var _sum=SUMX(ALL(data),[Col_Frequency]) var _segment= SWITCH( TRUE(), [Cummulative sum]<=0.25*_sum,"A", [Cummulative sum]<=0.5*_sum,"B", [Cummulative sum]<=0.75*_sum,"C", [Cummulative sum]<=_sum,"D", BLANK()) return _segment- Place the [dyanmic segmentation] into the table chart, and you can get what you want, like this:
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous , You have to create an independent table with ranges. Using this new table col GT Frequency and values(Table[MUID]) you have to create new measures that use this bucket.
please refer to my video and file(attached after signature) : https://www.youtube.com/watch?v=CuczXPj0N-k
or refer segmentation
https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
