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?

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?

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?

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