cancel
Showing results for
Did you mean:
Helper II

## 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

1 ACCEPTED SOLUTION
Community Support

Hi, @skondi

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:

1. 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``````

1. Place the [dyanmic segmentation] into the table chart, and you can get what you want, like this:

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 2
Community Support

Hi, @skondi

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:

1. 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``````

1. Place the [dyanmic segmentation] into the table chart, and you can get what you want, like this:

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.

Super User IV

@skondi , 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.

or refer segmentation

Proud to be a Super User!

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!