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.
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 Candy 6 14 50 Soda 8 18 42 Chips 4 21 66
I think I'll have to use an LOD calculation? Not sure how to format it, though.
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
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!
Percentile Test.twbx 15.1 KB
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.
Hey, yes number of orders. (Which should also equal the number of records since 1 order = 1 row).
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!
Percentile Test_v10.2.twbx 23.0 KB
Exactly what I was looking for, Don. Double checked the math and everything looks good!
Thanks a bunch.