cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

How to Make segments

Hi all, I am stuck at some dyanmic segmentation part.

 

Screenshot :

skondi_1-1604301241681.png

 

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

v-robertq-msft_0-1604397029167.png

 

  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:

v-robertq-msft_1-1604397029177.png

 

 

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.

View solution in original post

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

v-robertq-msft_0-1604397029167.png

 

  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:

v-robertq-msft_1-1604397029177.png

 

 

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.

View solution in original post

Super User IV
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.

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors