14 Replies Latest reply on Jan 25, 2012 1:25 PM by Richard Leeke

    Calculating Percentiles?

    Dan Cole

      Can tableau calculate the value that represents the x percentile of a data set (similar to the excel Percentile() function)?

       

      I currently am creating a line graph using Running Sum Of Total of Count with a 2nd calculation of Percent of Total to create charts, then exporting the data to access and then looking for the datapoint closest to the percentile that I'm looking for and then creating a report from the access database. This would be much easier if tableau could calculate this natively.

       

      Dan Cole

      danc@carilion.com

        • 1. Re: Calculating Percentiles?
          Erin Easter

          Hi Dan,

           

          This may be what you are asking to do but you can add reference lines that mark a specific location or region on the axis. For example, you can add a  reference line at the 90th and 95th percentiles. Then you can quickly see the data points that lie in those regions on the axis.

           

          Add a reference line by right-clicking the axis in your view and selecting Add Reference Line. In the subsequent dialog box, specify the line type to be a computed percentile and then specify the percentage. when finished, click OK.

           

          Does that help?

          • 2. Re: Calculating Percentiles?
            Dan Cole

            It helps visually, but I was hoping to be able to show the resulting amount as values not necessarily graphically, or if graphically, just the points that represent specific percentiles (25, 50, 75, etc) and not every point in between.

             

            I go through my MSAccess step to sift through all the points of data from the data export function to just get the percentiles/quartiles that I need.

             

            I'd like to think that others analyzing process data would like to see how, for example, the 50th%'ile changed over time.

             

             

            Dan

            • 3. Re: Calculating Percentiles?
              . HealthMaps

              It would be very, very useful to see how percentiles change over time. This is really a poor man's quantile regression, or at least could be a very effective picture of something similar.

               

              Seems Tableau needs percentile and median functions that could be added as default aggregations as well as functions for making new variables.

               

              Seems any function that is in Excel should be in Tableau.

              • 4. Re: Calculating Percentiles?
                guest contributor

                Could you please help me find the percentile, interquartile and semiquartile of these data

                 

                Set 1,8 11 12 12 16 25 23

                Set 2,16 16 8 8 7 12 14 11

                Set 3,17 18 18 21 25 26 15 16

                 

                Thank you

                • 5. Re: Calculating Percentiles?
                  guest contributor

                  Is this table in Tableau yet?

                  • 6. Re: Calculating Percentiles?
                    guest contributor

                    Dan,

                    I just encountered the same problem...would love to be able to display 25th, 50th, 75th and 95th Percentile the sameway you can dispay Min, Max and Avg, ie offer more then just reference line. In many statistical reports 50th percentile is more important than the average. Hope the crew from Tableau adds this feature.

                    thanks

                    Dinko

                    • 7. Re: Calculating Percentiles?
                      . richpauir

                      I assume there is still no capability for adding a percentile calculation, right?

                      • 8. Re: Calculating Percentiles?
                        Richard Leeke

                        I wrote a long, complicated, discussion of the options I could find a few months ago, here:

                         

                        http://www.tableausoftware.com/forum/calculating-percentiles-rawsql-or-custom-sql-connections

                         

                        Bottom line is you can do it easily if your back-end database supports it - which to my knowledge means you need to be using Oracle.

                        • 9. Re: Calculating Percentiles?
                          . richpauir

                          Richard Leeke, thanks for the post. 

                           

                          I think what the original poster was interested in, as well as myself, is preferably a native Tableau calculation or at least a way to call the Excel functions.

                          • 10. Re: Calculating Percentiles?
                            Richard Leeke

                            Yes, I wanted a way of doing it natively in Tableau, too.  But Tableau doesn't provide that - because all calculations on the underlying data are done in the back-end databases.  That is why I spent some time looking at what can be achieved using the different database back-ends.

                             

                            The only way that I can see that you would be able to call the Excel PERCENTILE() function would be to create a set of worksheets in Excel which used the function in cell formulae at whatever levels of aggregation you needed.  Each Excel sheet would support one particular set of dimensions on shelves in a Tableau viz (since it's not meaningful to do further aggregations on percentile values).

                             

                            I think you'd probably need to pivot the data in Excel and then use the PERCENTILE() funcction over data ranges in the pivot table.  So that would just be using Tableau to visualise analysis done in Excel - but I guess it might be useful sometimes.

                            • 11. Re: Calculating Percentiles?
                              . brandon

                              I'd be interested in this as well.  I'm looking to have Tableau calculate quartiles from my raw data set (in excel) without requiring that I run those calculations in excel.  This would be a great feature if someone could figure out how to run the calcs.  I guess you could use the standard deviation and average to calculate percentiles in some combination but having the actual 25th, median, 75th, etc would be very helpful.

                              • 12. Re: Calculating Percentiles?
                                Leigh Fonseca

                                Identifying percentile functions is exactly what I would like to do today. I am using Tableau 5.2 and could not find any such function (in the product or in help).  I'm trying to develop all my work in Tableau so I can easily hand it off to my product team.  Once I have to export and have more than 1 data file things get lost and mixed up.

                                 

                                Any chance adding percentile/quartile functionality is slated for the 6.0 release?  And if not, has it been added to the product backlog?  This would be extremely useful for the analyses I'm working on.

                                 

                                Thanks!

                                • 13. Re: Calculating Percentiles?
                                  Jamie Irizarry

                                  Is there any way to calculate the 95th percentile, for example, of the data in a column?

                                   

                                  Thanks

                                  • 14. Re: Calculating Percentiles?
                                    Richard Leeke

                                    This is an old thread dating back to before Tableau version 6.0 came out.  Version 6.0 has table calculations which make it possible (though its still a bit tricky).  I have written up a couple of goes at doing this for the Tableau Calculation Reference Library (one of the forums).  The second attempt (don't bother with the first way), is here.