Percentile Distribution

[neeleshapatil1]

Hi

I have the data of customers and their Sales by Category and Sub-Category. Screenshots shown below of Tableau Superstore Sales data. Please ignore red shading.

We want to assign customers to a percentile buckets like "First", "Second", "Third" , "Fourth" and "Inactive" based on their Sales within particular Category against the entire population’s. In-actives are with Zero Sales

Similarly bucketing based on their Sales within Particular Sub-Category when users will see the data /drill-down at Sub Category level.

First- 100 to 75th Percentile

Second - 50 to 75th Percentile

Third - 25th to 50 Percentile

Fourth - 0 to 25th Percentile

Inactive - Customers with Zero sales within that Category/Sub -category are classified as In-actives.

We want to pre-calculate these bucketing to avoid run time calculations at Tableau for better performance and understand its possible at run time.

Any suggestions would be really helpful.

[BradWerner]

Hi @neeleshapatil1!

I built out a workflow that calculates the percentiles for each customer at the Category and Sub-Category levels using Tableau 2020.2 Superstore data (see attached packaged workflow).

For reporting in Tableau, the most straightforward approach I see with this data structure is to create a view for Category Percentile Groups and another for Sub-Category Percentile Groups and use either dashboard actions or Viz in Tooltip to show the relationship between the two. I attached a packaged workbook with examples of both just for fun (EDIT: apparently you cannot upload .twbx files... weird. Let me know if you need help with this part and I can explain in more detail).

This was an interesting problem! Though I think Tableau's percentile table calculation is the most flexible as it allows you to calculate percentiles based on the granularity of your visualization, I did want to see what would happen if I built out what you suggested.

Let me know if you have any questions or if I missed the mark on something.

Brad

[neeleshapatil1 · Accepted solution]

Thanks Brad for your help here. 

Could you please confirm what;s the reason to use Cross-tab, transpose and Split tools. Is it just to generate rows for categories/sub categories where customers don't have sales?

If take few examples like for customer Alex Avila then dont see record from true output of IsNull([Sales]) filter tool int Category Sales percentile section. which records then marked as Inactive? 

also can you please help uploading your Tableau workbook on Tableau public and share link to look at it?

[BradWerner]

Hi @neeleshapatil1,

Right. The Cross Tab and Transpose piece allows us to get rows for categories/sub-categories where customers don't have sales. Without this, we would have a number of people that would not be marked as Inactive within a particular group. I looked into it and only 617 of the 793 have sales in all three categories.

I used the Text to Columns tool to split a column I had concatenated earlier. I did this in case your end data had sub-category names that show up in multiple categories. Not needed in Superstore but you might want to account for it in case it ever happens in your data.

Here is a link to the basic set up in Tableau: 

https://public.tableau.com/views/PercentileDistributionReportinginTableau/DashboardDrillDown?:langua...

Let me know if you can't download it or have questions.

Thanks!

Brad

[neeleshapatil1]

Hi Brad

Thanks for uploading Tableau workbook.

I have created one calculated field into Tableau titled as  Category Percentile Levels with formula as [Category]+"-"+[Category Percentile Group]

Users can select multiple options here but Is there way i can restrict the data for intersection of results after this filter selection.

I have highlighted rows expected after selecting Furniture - First and Office Supplies -Third.

Appreciate your inputs here