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

# Sorting based on Percent of Total over time.

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.

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.

• ###### 1. Re: Sorting based on Percent of Total over time.

Hi Ryan,

Thanks to the hack of Russell Christopher here, I managed to get your sorting. I tried then to get the top N, but hard luck! Hope this gets you going anyway...

• ###### 2. Re: Sorting based on Percent of Total over time.

Can you show me a picture of you sorting them?

• ###### 3. Re: Sorting based on Percent of Total over time.

I added another calculation <test>, with the formula: [Total % Paid in Interval]*-1, put it on rows, and removed the check mark from "show header".

• ###### 4. Re: Sorting based on Percent of Total over time.

This should satisfy the Top N and Sort requirement.  Let me know if I missed anything.

I left the Top N Field on the Rows shelf to show you how it's being computed but it doesn't need to be there.

• ###### 5. Re: Sorting based on Percent of Total over time.

Aaron,

I never paid attention to the subtle difference between index() and rank(), and what these functions can achieve. Your workbook was an eye-opener! Thanks!