6 Replies Latest reply on Nov 2, 2014 7:08 AM by Aaron Clancy

    Sorting based on Percent of Total over time.

    Ryan Sparks

      Hello Tableau Experts,

       

      When I first started this workbook I did not foresee this being a problem, but I cannot sort my data based on a certain calculated field. I thought that this would be easy but it has proven to be quite elusive.Payment Photo.png

      Here is a picture of what I am working with and I will attach my workbook below so maybe you can play with it. I will give an explanation of the workbook before I give my formal inquisition.

       

      So in the first column are different types of products which are numbered to differentiate them. I am going to skip the second column for now. As you can see this workbook is displaying data over time. Each month people buy the product and there is a payment amount (or sales amount) and that is the first number in the box. For example, for product #1 there was $21 million in payments/sales for the month of September. Now there exists a few underlying filters and calculated fields which are not displayed in the photo. I have filtered by a time interval, in this case it is between September and December. This matters because it will explain the second entry under the payment amount (pay no heed to the color). That percentage represents a preserved percent of total over the entire year. I had to make a calculated field to get this because if I did an Analysis percent of total it would change with the filter. The calculated field is as follows:

       

      sum([Payment Amount])/total(sum([Sheet1 (Sample Product Data) (copy)].[Payment Amount]))

       

      I made a duplicate of the original data source to preserve the total payment amounts for the entire year. So let's look at product #1 again, the payments in September represent 4% of the total payments for the entire year not just the total payments for September through December interval. This brings me to column 2, which is the summation of each month's (within the interval) preserved percent of total. Again we can look at product #1, the total payments over the interval (September - December) represents 69% of the total payments over the year. I had to make a calculated field for this too. Which is as follows:

       

      total(sum([Payment Amount]))/total(sum([Sheet1 (Sample Product Data) (copy)].[Payment Amount]))

       

      What I want to do with this workbook is to make a parameter that displays the Top N total(summed) percent of total (2nd column) for whatever time interval I choose. That is the endgame, but I cannot even sort this data based on the total(summed) percent of total in ascending or descending order. In the photo, product #15 should be at the top because 81% of its payments/sales came in during that specific time interval, then product #1 and so on and so forth. If you play around with the workbook you will notice how the percents change with the intervals. I have attached the workbook and the underlying excel spreadsheet in case anyone wanted to look at that too. Please help.