1 of 1 people found this helpful
As of roughly Tableau v9.2 all the percentile calculations in Tableau – PERCENTILE(), WINDOW_PERCENTILE(), RANK_PERCENTILE(), and percentile reference distributions – use the R-7 algorithm also used by MS Excel Quantile - Wikipedia, here's how it works with the 4 rows in your example:
Value = x
Sorted the values x1, x2, x3, etc. are 1, 3, 6, 9
number of values N = 4
percentile p = .25
index h = (N-1)*p + 1 = (4-1) * .25 + 1 = 1.75
If h was an integer then having ordered the values as 1st, 2nd, 3rd, 4th then the percentile result would be the hth value. However since h is a decimal this method uses a linear interpolation formula between x values that are the the floor(h)'th value and the ceiling(h)'th value. The way I think of this is that we're going to travel h% along the number line between those two x values.
In this example, h is 1.75 so the 25th percentile will be linearly interpolated between the 1st value (1) and 2nd value (3), and since the decimal part is .75 we're going 75% of the way from the 1 to 3 and the "distance" is (3-1) * .75 = 1.5, then finally the 25th percentile is 1 (the 1st value) + 1.5 = 2.5.
More generally put the R-7 formula for percentile result Q = (x for the integer hth value) + (decimal portion of hth value) * ((x for the (integer hth value + 1) - (x for the integer hth value)).
WINDOW_PERCENTILE(expression, number, [start, end])
Returns the value corresponding to the specified percentile within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
WINDOW_PERCENTILE(SUM([Profit]), 0.75, -2, 0))returns the 75th percentile for SUM(Profit) from the two previous rows to the current row.
If that doesn't help, if you can post an example TWBX file, I'll take a look.
Thank you for the quick and thorough response!