Percentile Distribution

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.

Preparation

Accepted answers

neeleshapatil1

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?

All comments

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

Percentile Distribution.yxzp

neeleshapatil1

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?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703