9 Replies Latest reply on May 16, 2018 5:10 AM by Vivek Shekar

    How to show Top 10% by Sales

    Vivek Shekar

      Hi All,

       

      I am using sample superstore data, my view is very simple, it has Category and Sum(Sales).

      Now for each separate value of Category, I want to get Sales of Top 10% of Customers for that value,

      For example, if Category = Furniture, I want to know within that value what is the top 10% sales by customer, but my view should not change.

      I mean it should have only Category and Sum(Sales).

       

      Any help on this would be appreciated

        • 1. Re: How to show Top 10% by Sales
          anand mishra

          hi vivek,

          I wish if u could explain this sentence more to clarify your doudt : "what is the top 10% sales by customer "

          Though you can look at the attachment and let me know if its helpful

          1 of 1 people found this helpful
          • 2. Re: How to show Top 10% by Sales
            David Maning

            Hi Vivek,

             

            I am quiet sure if i got your point right. Do you need to show the names of those customers? If so please find in the attachment.

             

            Trust this helps.

            D

            • 3. Re: How to show Top 10% by Sales
              Vivek Shekar

              Hi David,

               

              When you pick a category, lets say "furniture", for that particular value we may have "x" number of customers with different Sales value.

              So what I mean here is we need to show only Top 10% of Sum(sales) for that particular Category. But we can't have customer names in the view, thats the catch.

               

              Thanks,

              Vivek

              • 4. Re: How to show Top 10% by Sales
                Vivek Shekar

                Hi Anand,

                 

                When you pick a category, lets say "furniture", for that particular value we may have "x" number of customers with different Sales value.

                So what I mean here is we need to show only Top 10% of Sum(sales) for that particular Category. But we can't have customer names in the view, thats the catch.

                 

                Thanks,

                Vivek

                • 5. Re: How to show Top 10% by Sales
                  Vivek Shekar

                  Hi All,


                  I am able to get only Top 10% of customers by sales for a particular category, lets says furniture.

                  But I cant remove customer name from the view because everything goes out of place.

                  I simple, my view should have Category and Sum(sales), thats sum(sales) should Top 10% within that particular category.

                   

                  I am also attaching the workbook.

                  Hope this helps.

                  Also please use tableau 10.5.x to create workbooks, because I don't have Tableau 2018

                  • 6. Re: How to show Top 10% by Sales
                    David Maning

                    Vivek,

                     

                    Just tell me the diff between top 10% of sales and just 10% of sales?) Hint: there is no such diff =) 10% is just 10%. So just take LOD sum sales of Category and multiply it by 0.1

                     

                    { FIXED [Category]:SUM([Sales])}*0.1

                     

                    D

                    • 7. Re: How to show Top 10% by Sales
                      Zhouyi Zhang

                      Hi, Vivek

                       

                      Is it something like this? if yes, please find attached workbook.

                       

                      Hope this helps

                       

                      ZZ

                      • 8. Re: How to show Top 10% by Sales
                        anand mishra

                        hi,

                        hope this will finally help u....

                        • 9. Re: How to show Top 10% by Sales
                          Vivek Shekar

                          Hi All,

                           

                          Thanks a lot for your prompt responses it means a lot.

                          Anyway I found the right answer to the question posted. We have to use Percentile function and LOD. Below are the details:

                           

                          IF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .1) } THEN "<10%"

                          ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .2) } THEN "20%"

                          ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .3) } THEN "30%"

                          ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .4) } THEN "40%"

                          ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .5) } THEN "50%"

                          ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .6) } THEN "60%"

                          ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .7) } THEN "70%"

                          ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .8) } THEN "80%"

                          ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .9) } THEN "90%"

                          ELSE "100%"

                          END

                           

                          Also to get Top 10% we have to do the following calc, add that to filter self and select "True":

                           

                          {FIXED [Customer Name] : SUM([Sales])} >

                          {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .9)} AND

                          {FIXED [Customer Name] : SUM([Sales])} <=

                          {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, 1)}

                           

                          Also to get bottom 10% we have to do the following calc, add that to filter self and select "True":

                           

                          {FIXED [Customer Name] : SUM([Sales])} >

                          {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, 0)} AND

                          {FIXED [Customer Name] : SUM([Sales])} <=

                          {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, 0.1)}

                           

                          Again Thanks a lot for your help guys.