13 Replies Latest reply on May 13, 2019 5:23 PM by Rupali Singh

    Top 80% of Spend - count distinct customers

    Stephen Brehm

      As an example, I used the Tableau Sample-Super Store data in the attached workbook to lead up to my question:

      • On the first tab (data-Consumer-detail), I show that filtering on Year (2016) & Segment (Consumer) results in a list of 12 customers
      • On the second tab (data-Consumer-top4), I show how the built-in Top feature for filter allows me to pair down the list to show Top 4
        • Note: I am curious why do I have to 'Add to Context' the filters in order to show the 4 (but I'll save that inquiry for another post)?
      • On the third tab (data-Consumer-top80pct), I am stuck and need help. I want this tab to answer: How many unique customers represent 80% of total spend (based on year/segment filter)?

       

      Question: I know the answer is 4 customers represent 80% (actually 86%) of the total spend for Consumer in 2016, but how do I get this last sheet to give me that answer?

      Requirement: I need to be able to just specify the percent (for example 80) and then the number should change accordingly (from 12 which is 100%) to X (based on % indicated)

       

      I appreciate any advice you can provide

        • 1. Re: Top 80% of Spend - count distinct customers
          Jim Dehner

          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

          • 2. Re: Top 80% of Spend - count distinct customers
            Stephen Brehm

            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

              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

                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

                • 5. Re: Top 80% of Spend - count distinct customers
                  Jim Dehner

                  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

                    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 "data-Consumer-top80pct" 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 "data-Consumer-top80pct" 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 "data-Consumer-top80pct" 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

                     

                     

                    2 of 2 people found this helpful
                    • 7. Re: Top 80% of Spend - count distinct customers
                      Yuriy Fal

                      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

                      • 8. Re: Top 80% of Spend - count distinct customers
                        Okechukwu Ossai

                        Hi 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

                         

                        1 of 1 people found this helpful
                        • 9. Re: Top 80% of Spend - count distinct customers
                          Yuriy Fal

                          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

                            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

                              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

                                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

                                  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?