Monday, November 22, 2021

Add Percentile in a Pivot Table

Pivot Tables are a fantastic analytic tool in Excel to display data in a easily consumable format. They can quickly aggregate data into sums, averages and even standard deviations in the values fields of the pivot table. What if you wanted to rank data? Not by sorting, but in some "statistical" way? What if you wanted to get percentiles (like the 90th or 95th percentile)? You could create your own table with the PERCENTILE.INC or PERCENTILE.EXC functions to calculate it based on your specific criteria but then you'd need to maintain or update the range where those formula reside when there is new data. You can actually put percentile (or even quartile) data into a Pivot Table with the use of the data model and some DAX. Though data model and DAX sounds complicated, it's actually not to hard to use to get percentiles. See the video to learn how.

No comments:

Post a Comment