11 Replies Latest reply on Nov 13, 2017 7:29 AM by Shinichiro Murakami

# Find Top X Percent On Unsorted Data

I am trying to get an accurate running sum even when the data isn't sorted correctly. E.G. see below (left), notice how the running sum is based on how the sales are sorted. In this case, it is giving me a running sum, but not the running sum by highest sales. See below (right), I sorted the sales and now the running sum is correct. The issue is, folks will filter every which way, causing the running sum to change. How do I get the running sum to compute by highest value regardless of filters?

Workbook attached.

Left                                                                                                    Right

• ###### 1. Re: Find Top X Percent On Unsorted Data

Create a calculated field and give it a rank to always put you in order for sales, if you like, attach your board with the .tbwx extension

• ###### 2. Re: Find Top X Percent On Unsorted Data

See attached for tbwx.

• ###### 3. Re: Find Top X Percent On Unsorted Data

solution

• ###### 4. Re: Find Top X Percent On Unsorted Data

I am on 10.1 for the time being and cannot open what you sent. Anyway you can resend in 10.1? OR is there a different way for me to open?

• ###### 5. Re: Find Top X Percent On Unsorted Data

Hi Stepha,

I don't quite understand your request , but guess this?

Filtering the View Without Filtering Underlying Data | Tableau Software

Or if you are talking about top X within filtered scope, you can change filters to context.

Thanks,

Shin

• ###### 6. Re: Find Top X Percent On Unsorted Data

Shin, first off.. Thanks for your help!

Second, attached is an updated tbwx that may make more sense.

Notice the parameter on the right which dictates spend by %. E.G. if you have it at 80%, then only the top 80% of spend will show based on a running sum/total sum. Here is the issue, if someone sorts on quantity, then the running sum/total sum won't be sorted in the proper order anymore. So it will show a lot of states will low sales that don't actually make up the top 80% of spend.

How do I set this up so that no matter how this is sorted, or what it is sorted on, the top 80% will always be based on the highest sales down?

• ###### 7. Re: Find Top X Percent On Unsorted Data

Hi Stephan,

You should use "Custom sort" in that case.

Thanks,

Shin

• ###### 8. Re: Find Top X Percent On Unsorted Data

I think we are close, but not quite there.

Let me explain it this way - I need to have top X % by total sales. Once I have my top X %, then quantity should be sorted automatically high to low.

E.G. Total sales on based on top 50% of total sales, quantity is sorted high to low based on what the top 50% of total sales are.

• ###### 9. Re: Find Top X Percent On Unsorted Data

Just sort.

Shin

• ###### 10. Re: Find Top X Percent On Unsorted Data

Hey Shin,

See your screenshot below. I added some blue lines. This is the issue I am referring to. If you sort on quantity, the sales are not in the correct order. Therefore, if you want top X % of sales, then show me the corresponding quantity to that top X %, this won't be correct. The sales are not in order. The first blue line, (\$138,641) should go up, etc. If that moves up, the running sum on the right will change. I am basing my top X % on the running sum.

Does that make sense?

• ###### 11. Re: Find Top X Percent On Unsorted Data

HI Stephan,

I'm sorry I don't understand you request quite well.

Shin