3 Replies Latest reply on Aug 11, 2018 8:47 AM by Jim Dehner

# Finding the top 10%,25%,50%,75% users and using that as a dimension

Hi there,

Tableau noob checking in here.

I have a certain analysis that I have done on Excel but i would like to replicate on Tableau. There is a dump of unique user data that consists of unique ID, acquired month, event month and minutes watched. I would like to categorise the users based on their minutes consumed (top 10%, 25%, 50%, 75%). In Excel, to this effect I would sort by descending order of minutes watched and create a running total of contribution (cumm minutes %) and segregate them manually based on the above number criteria.

the end table would be ideally look something like this

 User bracket Count of ID Sum of Minutes Sum of Views 1_0% to 10% 5,725 2,422,826 427,540 2_10% to 25% 11,738 3,634,189 604,623 3_25% to 50% 22,517 6,057,176 968,541 4_50% to 75% 28,413 6,057,088 1,022,667 5_75% to 100% 98,908 6,057,323 1,160,687 Grand Total 167,301 24,228,601 4,184,058

the other thing that I would like to see on Tableau is to create a bar graph with acquisition month on x-axis and user bracket as color. something like this:

After checking the forums, I notice that the running_sum formula can not be used here since the user minutes has to be an aggregated function.

user minutes = {fixed [ID]: sum ([minutes])

What would be an elegant method to perform the same analysis as done on excel in Tableau?

The main reason I would like to shift to Tableau is that this analysis will have to be scaled to multiple datasets that will be very cumbersome on Excel.

• ###### 1. Re: Finding the top 10%,25%,50%,75% users and using that as a dimension

Hi

I am attaching a workbook I created for someone that want to break customers into groups by sales quintiles -

the process is a little involved but you can use it as a template to meet your problem

the red tab summary page returns this

the Save this tab is the detail

you can work your way through the formulas - they all table calculations which adds to the difficulty

If you need to discuss the solution feel free to reach out

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Finding the top 10%,25%,50%,75% users and using that as a dimension

Thank you so much for your response, Jim.

I liked your approach and though it took some time to figure this out, I think i succeeded in creating the correct ranking table like the one you shared

but here is a question. As per my initial table that posted earlier, I wanted to find the total minutes within each bucket. the table pasted above gives a running total instead. I am finding that a bit difficult to aggregate since the bucketing is not an aggregation but a view filter. any thoughts on how to do this?

• ###### 3. Re: Finding the top 10%,25%,50%,75% users and using that as a dimension

hi see the attached

it returns this

it uses these

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.