
1. Re: Top 80% of Spend  count distinct customers
Jim Dehner May 19, 2017 1:03 PM (in response to Stephen Brehm)Hi Stephen
Tableau has a function that almost works  have you used Window_ Percentile
WINDOW_PERCENTILE(SUM([Sales]),.8) It will calculate the Value of sum(sales) between the 80% and the next highest %
see below
The value in this string is 650  there are 3 above 650 the remainder below
Then you can count the number of customers names with
window_sum(if sum([Sales]) > [percentile calc] then countd([Customer Name]) end)+1
Now it is an almost solution because it is possible that you could have a mark at exactly 80%
Let me know if this helped
Jim

Top80pctCount.twbx 1.2 MB


2. Re: Top 80% of Spend  count distinct customers
Stephen Brehm May 19, 2017 2:51 PM (in response to Jim Dehner)I tried using the attachment in your reply but it does not have the calculations you outlined. Can you update the .twbx file for me to review?

3. Re: Top 80% of Spend  count distinct customers
Jim Dehner May 19, 2017 3:37 PM (in response to Stephen Brehm)They are doing maintenance at the site
but I have attached it here
see the red tab
Jim
On Fri, May 19, 2017 at 4:52 PM, Stephen Brehm <tableaucommunity@tableau.com

4. Re: Top 80% of Spend  count distinct customers
Norbert Maijoor May 20, 2017 4:25 AM (in response to Stephen Brehm)Hi Stephen,
Find may alternative approach provided by Jim Dehner below and stored in attached workbook version 10.2 located in the original thread.
1. Running Sum Sales: RUNNING_SUM(SUM([Sales]))
2. Total Sales: {fixed:sum([Sales (copy)])}
3. % Running Sum Sales: [Running Sum Sales]/sum([Total sales])
4. Show??: [% Runnning Sum]<=0.86
5. Drag the required objects to the indicated positions and set filter Show?? to True
Regards,
Norbert

Rank Running Sum_nalmai.twbx 1.3 MB


5. Re: Top 80% of Spend  count distinct customers
Jim Dehner May 20, 2017 3:58 AM (in response to Norbert Maijoor)Hi Norbert
One of the really good things about Tableau is that there are many ways solve the problem 
Thanks for the mention
Jim

6. Re: Top 80% of Spend  count distinct customers
Okechukwu Ossai May 20, 2017 5:12 AM (in response to Stephen Brehm)Hi Stephen,
Like Jim said there are different approaches to get this done and here is a slightly different approach to get you the result you want in your "dataConsumertop80pct" tab.
Below are the suggested steps;
1. Create a calculated field [Cum. % of Total Spend]
RUNNING_SUM(SUM([Sales])/WINDOW_SUM(SUM([Sales])))
This is a cumulative or running sum of the percent contribution of each customer to the Total Spend. Since this is a table calculation, you need to right click and set "Compute Using" as required. In this case it is Table (Down).
2. Create a calculated field [Top 80% Threshold]
WINDOW_MIN(IF [Cum. % of Total Spend] >=0.8 AND [Cum. % of Total Spend] < 0.9 THEN [Cum. % of Total Spend] END)
You've been asked to find the Top 80% Spend. However, there may not be an absolute 80% value in your [Cum. % of Total Spend], just like we have 85.69% in this example. What the formula above does is to check [Cum. % of Total Spend and return the minimum value in the 80s. This will be 80% if present or 85.69% in this case. Put this in the view, right click and select Compute Using Table (Down).
3. Create calculated field [Top 80% Distinct Count]
WINDOW_COUNT(IF NOT [Top 80% Threshold] < [Cum. % of Total Spend] THEN ATTR([Customer Name]) END)
This formula calculates the distinct count of customers contributing to the Top 80% of total sales. It checks if Cum. % of Total Spend is greater than Top 80% Threshold, then it will not count the customer, otherwise it will count the customer. "IF NOT <" was used which is the same as the explanation above just in a different notation. You will understand what this reverse notation does when you check the attached workbook and inspect the field called [In Top 80% Distinct Count?]. Put these calculated fields in the view, right click and select Compute Using Table (Down).
4. Create calculated field [Row Filter]
FIRST() == 0
Your main objective is to summarize your result in the "dataConsumertop80pct" tab. To do this you need to have a way to filter out all the customer names and just leave the first row of data. This is what Row Filter helps you achieve. I have built this visualization for you in the "dataConsumertop80pct" tab. Add [Customer Name] to the filter shelf and sort by field , SUM( sales)  descending. Then Add [Customer Name] again to the Detail marks. Add [Top 80% Distinct Count] to the Detail marks and select Compute Using Customer Name. Finally add [Row Filter] to the filter shelf; set to True and select Compute Using Customer Name. Set to "True" if prompted again.
5. For your question about context filters, you can read this Tableau online help material for more information. Improve View Performance with Context Filters
Notice that there are many Segments and Years in the database. What the context filters do is to ensure that Tableau will first filter the data to contain only customers who purchased items in the Consumer segment in 2016 before applying your Top 80% Spend filter.
See attached workbook in version 10.0. Hope this helps.
Ossai

Top80pctCount_OO.twbx 1.2 MB


7. Re: Top 80% of Spend  count distinct customers
Yuriy Fal May 20, 2017 11:58 PM (in response to Jim Dehner)BTW, there is a calc in Tableau exactly for that,
and it is a RANK_PERCENTILE(), of course.
Everything else is like Jim had suggested (count plus 1).
Please find the attached.
Yours,
Yuri

Top80pctCount_YF.twbx 1.2 MB


8. Re: Top 80% of Spend  count distinct customers
Okechukwu Ossai May 21, 2017 2:45 PM (in response to Yuriy Fal)1 of 1 people found this helpfulHi Yuri,
I'm struggling to see how Rank Percentile and Count plus 1 will work here. Could you please take a look at this again? I guess it was a coincidence that Rank Percentile worked for this example. What will happen if the question was Top 90% of Total Spend or Top 30% of Total Spend? I have added a screen shot which sows what Rank Percentile calculates.
The question again is;
How many unique customers represent 80% of total spend (based on year/segment filter)?
Total Spend is $4,646 and 80% of that is $3717. The first 3 customers purchased a total of $3515 which is 75.6% of Total Spend while Rank Percentile calculates 81.8%.
If the question was Top 90% of Spend, using this method will be wrong because Rank Percentile > 0.9 will be 2. Add 1 to that and that will be 3 customers. However, it is about 6 customers who contribute to 90% of Total Spend.
Ossai

9. Re: Top 80% of Spend  count distinct customers
Yuriy Fal May 22, 2017 8:13 AM (in response to Okechukwu Ossai)Hi Okechukwu,
You're absolutely right (and I'm completely wrong).
The RANK_PERCENTILE() doesn't apply here at all.
It's a pure coincidence (looks like a Pareto law in action).
So please disqualify my answer and move on.
Yours,
Yuri

10. Re: Top 80% of Spend  count distinct customers
Stephen Brehm May 22, 2017 2:25 PM (in response to Stephen Brehm)Thanks VERY much to everyone who replied. I ended up successfully using the approach provided by Okechukwu Ossai

11. Re: Top 80% of Spend  count distinct customers
Andy Dalugoda Mar 22, 2018 11:40 AM (in response to Okechukwu Ossai)i'm trying to use this solution to a problem i have
how did you sort customer name based on sum(sales) desc?
thanks,

12. Re: Top 80% of Spend  count distinct customers
Feifei Chen Sep 23, 2018 3:25 AM (in response to Okechukwu Ossai)Hi Ossai,
That is great answer! Thanks.
I have a more complicated demand  to count the top 80% contributors for different categories. I dropped [category] to column, the results were not exactly correct. But if I use category as filter, the counting is correct.
Would you please have a look?

13. Re: Top 80% of Spend  count distinct customers
Rupali Singh May 13, 2019 5:23 PM (in response to Okechukwu Ossai)Hi Okechukwa
Using WINDOW_COUNT(IF NOT [Top 80% Threshold] < [Cum. % of Total Spend] THEN ATTR([Customer Name]) END)
I got the distionct count of suppliers,now how can i calculate the total amount related to them.
So for example if 40 makes 80% of total spend,how much spend is that?
Also once i know that it is x amount how can i divide it with total money for the year?