1 2 Previous Next 18 Replies Latest reply on Feb 8, 2016 1:44 PM by Jim Wahl

    Percentile Calculation (P95) calculation in Tableau different from Excel

    Ademola Okuneye

      I'm using the P95 calculation function in Tableau and finding a significant difference between what Tableau and Excel results.

       

      Any ideas why such a big difference?

       

      Here's a comparison of the results

       

      Data SetTableau P95Excel P95
      A (26 Data Points)227222
      B (10 Data Points)193178
      C (53 Data Points)163156
      D (187 Data Points)217214
      E (80 Data Points)224221
      F (28 Data Points)275270
      G (21 Data Points)171171

       

       

       

       

      Thanks

       

      Message was edited by: Ademola Okuneye I have attached a sample data (twbx & excel) showing the differences I am getting.

        • 1. Re: Percentile Calculation (P95) calculation in Tableau different from Excel
          Ramon Martinez

          Hi Ademola,

           

          To get and answer to your question, I suggest you to share a sample data set to replicate your results and find the reason of the difference you have found..

           

          Best

          Ramon

          • 2. Re: Percentile Calculation (P95) calculation in Tableau different from Excel
            Ademola Okuneye

            Hi Ramon,

             

            I have attached the sample data (twbx & excel) which shows the results I'm getting.

             

            Thanks for you help!

            • 3. Re: Percentile Calculation (P95) calculation in Tableau different from Excel
              Matt Lutton

              Jim Wahl has written about Percentiles lately, and I wonder if he might have a clue as to why Tableau is computing this differently.  I tried several approaches, but could never get the values to match your Excel file.  I assume Excel is computing the Percentile using a different method, but I'm not exactly sure.

               

              Hope to see a solution from someone soon!

              • 4. Re: Percentile Calculation (P95) calculation in Tableau different from Excel
                Michael Boldin

                Look at the difference between Excel's PERCENTILE.EXC and PERCENTILE.INC functions

                I believe PERCENTILE is the Inclusive type (INC).

                In essence, where there are 'ties' and the percentile breakpoints do not perfectly split the data, percentile calculations have a degree of ambiguity that depends on how you count and use the 'last' and 'next' value around the percentile split.

                FYI your table in the posting and the EXCEL file do not agree exactly. See Group C

                1 of 1 people found this helpful
                • 5. Re: Percentile Calculation (P95) calculation in Tableau different from Excel
                  Ademola Okuneye

                  Thanks - I'm hoping someone has been able to replicate the PERCENTILE.INC function in Tableau

                  • 6. Re: Percentile Calculation (P95) calculation in Tableau different from Excel
                    Matt Lutton

                    Is there a trick to using Percentile.EXC in Excel?  I'm unable to use it--did it show up in 2010?

                    • 7. Re: Percentile Calculation (P95) calculation in Tableau different from Excel
                      Michael Boldin

                      Microsoft documentation says EXCEL 2010.  I believe a large number of extra .xxx functions were added in 2010 to fix problems with various EXCEL statistics calculations and handle ambiguities like this one (Google: "Friends don't let friends do statistics in EXCEL").   

                      • 8. Re: Percentile Calculation (P95) calculation in Tableau different from Excel
                        Michael Boldin

                        But EXCEL's PERCENTILE calculation might be wrong (or at least unconventional)!

                        Not sure if Tableau is right, but I'd trust it more.  There are numerous web postings about EXCEL's problems in computing statistics.  I suggest you work out by hand one of your cases where EXCEL & Tableau differ and decide which one you want.  The differences are typically from a different denominator ( /n  or /(n-1) ) or an interpolation formula)

                        • 9. Re: Percentile Calculation (P95) calculation in Tableau different from Excel
                          Ramon Martinez

                          I was playing with your data in Excel, Tableau and Alteryx. I was able to get the same results in Alteryx using the Percentile function (it seems is the same implementation than Excel).

                          Definitively Tableau uses a method to compute Percentile that is efferent to the method used by Excel.

                           

                          In Alteryx, we are able to select at least two methods to compute Percentiles: Percentile and PctNo0 providing different results.

                           

                          Percentaile page in Wikipedia presents information about methods to compute Percentiles, including the implementation in Excel.

                           

                          I've not fund the method used by Tableau so far.

                           

                          Best

                          Ramon

                          • 10. Re: Percentile Calculation (P95) calculation in Tableau different from Excel
                            Jonathan Drummey

                            This falls into the category of one of those "Things that I learned that I've never had time to fully detail and write up." There are 4 areas where Tableau computes percentiles (PERCENTILE, WINDOW_PERCENTILE, RANK_PERCENTILE, and reference lines/bands/box plots). I've got an email somewhere with details on the algorithms, Tableau actually uses a couple of different algorithms in its functions so results of different functions aren't necessarily convertible, and don't necessarily match what we'd see coming out of Excel. In large samples (like 1K values or more) the results of the different functions will converge, but in smaller sample sizes they can be wildly different as you found.

                             

                            Jonathan

                            • 11. Re: Percentile Calculation (P95) calculation in Tableau different from Excel
                              Jim Wahl

                              WINDOW_PERCENTILE will get you the Excel style percentile if you disable aggregate measures. This last part is critical and probably not something most people realize they need to do with WINDOW_ functions based on measures.

                               

                              Here's the formula:

                              Days P95 TC =

                              IF FIRST() == 0 THEN
                                  WINDOW_PERCENTILE(ATTR([Days]), .95)
                              END
                              

                               

                              ATTR() is required because table calcs, such as WINDOW_XXX, require aggregates. ATTR() is an aggregation where if MIN(x) == MAX(x) you get x, else you get "*". IF FIRST() == 0 is used to return the value for just the first row of of the Days partition (the calc is the same for all rows and in a table you just want one value).

                               

                              If you just put this calc in the view as I did below, you'll get NULL values because this view is at the category level of detail and there are multiple Days values for each category and, therefore, ATTR([Days]) is "*".

                              2014-07-28_17-05-56.png

                               

                              My next thought was to drag Days to the Detail button. Since days is a measure in your workbook, Tableau will add this as SUM(Days). This, of course, is not helpful, so you think you need to convert this a dimension. This gets you deceptively close (after adding, click the Days P95 TC field and change Compute Using to Days and then from the top menu bar select Analysis > Stack Marks > Off):

                              2014-07-28_17-12-46.png

                              But it's not quite right. P95 for category B should be 177.9 and above it's calculated as 179.8. The problem is that by default Tableau aggregates measure values. Category B consists of 10 values: 30, 53, 55, 93, 114, 114, 145, 152, 157, 195. Since two of these values are the same, when you convert Days to a dimension, Tableau returns 9 values not 10.

                               

                              You can see this by dragging Days on top of Days P95 TC (again, from the top menu select Analysis > Stack Marks > On).

                              2014-07-28_17-17-13.png

                               

                              To get the complete set of Days values, you need to deselect Aggregate Measures from the top Analysis menu:

                              2014-07-28_17-20-30.png

                               

                              Now you can drag Days P95 TC back to the view to get the correct WINDOWS_PERCENTILE() values that also mach the Excel algorithm for percentile:

                              2014-07-28_17-23-45.png

                               

                              TLDR --- Tableau's Percentile vs Windows_Percentile ---

                              For comparison here are your original values using the built-in percentile aggregation:

                              2014-07-28_17-26-36.png

                               

                              As others have said, there is no standard for percentile -- R's percentile function has 9 methods (3 for discrete values, 6 for continuous), but I bet you could find another half dozen methods in the various R packages --- even summary() is slightly different.

                               

                              The Excel method (R Type 7) for your example, where you want the 95th percentile with 10 values is

                              the h value in the data for p95 = (10-1)*.95 + 1 = 9.55

                               

                              Since h is a decimal, you use linear interpolation: take the 9th value + .55*(10th - 9th) = 157 + .55*(195-157) = 177.9.

                               

                              The Tableau formula (appears) to use proportional interpolation: 157 + .95*(195-157) = 193.1. (This is not one of the 9 R types, and doesn't really make sense to me, but it's hard to say it's wrong when there is no standard.)

                               

                              Tableau Reference Lines Percentile

                              Tableau uses a third method for reference lines, which is equivalent to R type 2 for discontinuous values. Where the h value is Np + 1/2. In your example this is 10*.95 + 1/2 = 10, so the 95th percentile is also the max value of 195.

                               

                              For group A, which has 26 values, h is 26*.95 + 1/2 = 25.2. Using this formula if the decimal is < .5 you round down. If it's > .5 you use the next value. If == 0.5, then you take the midpoint. For group A, you want the 25th value, which is 228.

                               

                              One nice thing about this algorithm is that the percentile is usually a measure value (unless it's at the midpoint).

                              2014-07-28_17-59-18.png

                               

                              Jim

                               

                              PS --- I used Tableau 8.2 for your workbook. If you're on 8.1, you'll get an error, but you should be able to work through the above manually. Let me know.

                              3 of 3 people found this helpful
                              • 12. Re: Percentile Calculation (P95) calculation in Tableau different from Excel
                                Ramon Martinez

                                Wow!! Jim,

                                 

                                Thanks for this very informative post about Percentile Calculation in Tableau.

                                 

                                Best

                                Ramon

                                • 13. Re: Percentile Calculation (P95) calculation in Tableau different from Excel
                                  Matt Lutton

                                  Awesome!  I got to the "deceptively close" result, but didn't realize aggregation was the problem.  Thanks for the thorough explanation, as always, Jim!

                                  • 14. Re: Percentile Calculation (P95) calculation in Tableau different from Excel
                                    kettan

                                    Here is a link to Jim's interesting explanation:

                                     

                                    How to make WINDOW_PERCENTILE equal to reference line percentile?

                                    1 of 1 people found this helpful
                                    1 2 Previous Next