2 Replies Latest reply on Nov 5, 2013 8:35 AM by Matthew Comb

    Making Quartiles based on Percent of Total Revenue, can you then do further calculations?

    Matthew Comb

      Hello, I believe my question may concern the issue of attempting to do table calculations upon table calculations.

      However, I think it is a question that would apply to a number of businesses, and would be useful, if it can be solved.

       

      I am trying to create a line graph broken down by quartiles and years.  For the division of quartiles, I want the number of products that make up 25% of the total revenue for that year to be in Quartile 1.  I want the next 25% to be in Quartile 2, etc..  I was able to segregate the data into quartiles using a table calculation and I have it in the attached workbook.  The problem is there is no easy way of determining the number of products in each quartile, and my goal is to create a graph using the counts.  You cant simply do a count distinct on product number by Quartile, because quartile is an aggregation.  Is it possible to do this only using tableau?  Here is an example of how I want the data to look, which would then be used to make a graph. 

              # products by year

            2006      2007     2008

      Q1: 15         20          20

      Q2: 100       100        200

      Q3:300        308        200

      Q4: 900       900        900

       

       

      There are two attachments, the sample product revenue data is the data source. The Sample data.twbx is the workbook. 

       

      Thanks, Matt Comb

        • 1. Re: Making Quartiles based on Percent of Total Revenue, can you then do further calculations?
          Ramon Martinez

          Hi Matthew,

           

          I've not seen your workbook and sample data set yet, but I would like to add a comment for your consideration:

           

          By concept, every quartile has the same number of products if you are calculating quartile at product level of detail, as you mentioned in your post.

          ... For the division of quartiles, I want the number of products that make up 25% of the total revenue for that year to be in Quartile 1.  I want the next 25% to be in Quartile 2, etc..

           

          I will take a look at your workbook

           

          best,

          Ramon

          • 2. Re: Making Quartiles based on Percent of Total Revenue, can you then do further calculations?
            Matthew Comb

            I must not have made it clear what I mean by Quartiles.   Basically what I want to do is determine which products are high sellers over the years.  Is most of the revenue coming from specific products?  So, I want to determine what percent of the total revenue each product makes up for that year. (ie product 1 is .02% of total revenue)  Then I want to take the highest percentage products (descending sort) and add them up until they make 25% of the total revenue for that year and put them into Quartile 1. Then use the same method, except it would be from >25% to just over 50%, and label those products as being in Quartile 2 for that year, etc…

            I hope that helps to clarify what I am trying to accomplish.

             

            Thanks for the quick response,

            Matt