8 Replies Latest reply on Apr 12, 2018 7:59 AM by Peter Hanges

# Count number of records in a percentile

Hello,

I have a measure called Amount, and would like to calculate the number of records above or below a certain percentile.  For example, if the 80th percentile is \$100,000, then I want to count the number of records that have an amount >= 100,000.

What would also be interesting to see, is the average value of Amount above or below a percentile.  An example question would be: What is the average Amount for records >= the 80th percentile.

Appreciate any help!

• ###### 1. Re: Count number of records in a percentile

I would start with a parameter for percentile selection in case you want to mover from 80% to 90% or anywhere else in the future.  Then you will need a calculated field that says something like:

IF PERCENTILE([Amount]) >= [Parameter] THEN COUNT(Number of Records) END

The second part would be something like the above, but you will need to divide the sum of the amount that is >= by the number of records.

• ###### 2. Re: Count number of records in a percentile

Hi Tim,

I don't want to use a parameter since I'll have multiple amounts shown in a table across various products (top 10%, top 25%, etc.).  Something like this:

Product
# of Sales Top 5% of Orders
# of Sales Top 10% of Orders
# of Sales Top 25% of Orders
Candy61450
Soda81842
Chips42166

I think I'll have to use an LOD calculation? Not sure how to format it, though.

• ###### 3. Re: Count number of records in a percentile

Do you know which percentiles you want to check for, or is that something you want to change at any time?

A workbook would make this much easier if you're able

• ###### 4. Re: Count number of records in a percentile

Hi Mike,

Yes, I know the percentiles I want to check for and they won't change.

I've attached a sample workbook:  Included in the workbook is a calculated field which shows the \$ Amount for the top 25% of orders, sliced by product.  I'd like to know two things:

By category, the number of orders which have an amount >= the top 25% \$ value.

By category, the average value of orders which are >= the top 25% \$ value.

Thanks for the help!

• ###### 5. Re: Count number of records in a percentile

Ok. Your original question said you wanted the 'Number of Records' above the 25th percentile. Do you mean the number of orders? ([Order Amount]). Number of records is an auto-generated field in tableau that is literally the number of rows in your data source. Just want to be sure before I proceed.

• ###### 6. Re: Count number of records in a percentile

Hey, yes number of orders. (Which should also equal the number of records since 1 order = 1 row).

• ###### 7. Re: Count number of records in a percentile

Hi Peter,

I got some inspiration from the following work by Michel Caissie Re: Count the number of records that make up the Percentile  and came up with the attached .twbx   Initially I based my percentiles using the standard percentile function in Tableau and also because of how the initial calculation had been done in the workbook; however, that method of percentile function only gets you the lower tier of the percentile.  So if you're calculating a 25th percentile using the percentile function in Tableau, it represents all values inclusive and BELOW that value, not above.

In re-reading your request, I realized you wanted the TOP 25%, which required a different method of calculating the result.  The workbook has both of the items you were looking for.  It's not elegant in terms of approach (calculation on top of calculation), but appears to work and I double checked the math on the averages.  Can you validate?  I did this in 10.5 but re-saved in 10.2 for you.

Maybe Mike Mainzer will also come up with something if this doesn't work for you...hope it helps!

Thanks, Don

• ###### 8. Re: Count number of records in a percentile

Exactly what I was looking for, Don.  Double checked the math and everything looks good!

Thanks a bunch.