9 Replies Latest reply on Jun 28, 2016 4:05 AM by Mattia Balzarini

# Segmentation in Percentile

Hello Tableau Community

I'm facing an issue. I have a set of users and I need to segment them according to a measure.

I want them segmented in percentile, according to the number of transactions they did or at least equally split. Let's say I have 1000 Users and I wanna be able to decide if I wanna them grouped each 100 (always according to number of transactions).

I manage to have it in a static way, but I want a dynamic solution that would work with different set of Users and different measure (sometimes might be sales, sometimes margin and so on)

Mattia

• ###### 1. Re: Segmentation in Percentile

Hi Mattia,

can you draw some input and desired result from the input which can help in dummy data creation and building the solution?

-Ashish

• ###### 2. Re: Segmentation in Percentile

Hi sure,

I want the Userid segmenting as percentile based on Number of records. Result should be a chart with on x axis the percentiles and on column the count of users

• ###### 3. Re: Segmentation in Percentile

Hi Mattia,

Till the time I am working on this, you can refer to this link. It might give you an answer that you are looking for.

-Ashish

• ###### 4. Re: Segmentation in Percentile

Hi Mattia,

Are you looking for something like this? Please find the attached below.

I have created a calculated field as below.

IF [Number of records]<={FIXED :PERCENTILE([Number of records],.1)} THEN "<10th"

ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],.2)} THEN ">10-20th"

ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],.3)} THEN ">20-30th"

ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],.4)} THEN ">30-40th"

ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],.5)} THEN ">40-50th"

ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],.6)} THEN ">50-60th"

ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],.7)} THEN ">60-70th"

ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],.8)} THEN ">70-80th"

ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],.9)} THEN ">80-90th"

ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],1)} THEN ">90th"

END

Let me know if this helps.

-Ashish

1 of 1 people found this helpful
• ###### 5. Re: Segmentation in Percentile

Yes I need something like you get. When I replicate it in my scenario something goes wrong. Don't understand why.

 Oh I guess it is because for you number of records is already aggregated for users, while for me it is not. I first have to calculate a column which gives me aggregation per user as in the article u linked

Formula

IF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.1)} THEN "<10th"

ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.2)} THEN ">10-20th"

ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.3)} THEN ">20-30th"

ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.4)} THEN ">30-40th"

ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.5)} THEN ">40-50th"

ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.6)} THEN ">50-60th"

ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.7)} THEN ">60-70th"

ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.8)} THEN ">70-80th"

ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.9)} THEN ">80-90th"

ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],1)} THEN ">90th"

END

• ###### 6. Re: Segmentation in Percentile

Ok it is working great.

Now the next step issue is as follow. This works on the full set of customers. But if I use just a portion of them it doesn't recalculate the division.

How can I make it dynamic based on the group of users I use as base?

For example I divide customers in old and new and medium as in excel attached

• ###### 7. Re: Segmentation in Percentile

Hi Mattia,

When you are taking tableau generated number of records its all falling in "<10th"  category since for every ID you are having value as 1. In the new data shared by you, do you want me to go with  Column C or you want me to use tableau generated number of record?

• ###### 8. Re: Segmentation in Percentile

It is fine this way.

The only problem I have now, is that this percentile seems to work based on the all users in my data set. If I filter them as for colums A. It doesn't seem to recalculate the percentiles for the smaller amount of users. Do you know how to make it dynamic to changes of the data set?

• ###### 9. Re: Segmentation in Percentile

Any idea on how to make the percentile division being recalculated for different users filtering?

Thanks

Mattia