How to Make segments

[Anonymous]

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 

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description, you want to add dyanmic segmentation based on the accumulate sum value, you can try my method:

1. Go to power query editor to add an index column:

2. 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

3. 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.

[amitchandak]

@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