KNOWLEDGE BASE

Calculating Inclusive Percentile Rank


Published: 07 Dec 2018
Last Modified Date: 19 Feb 2020

Question

How to calculate inclusive percentile rank in Tableau Desktop, which is the same as the PERCENTRANK.INC function in Excel.

Environment

Tableau Desktop

Answer

The attached example workbook, which you find in the upper right-hand corner of this page, uses the sample data set Superstore to demonstrate the following steps:

Creating the calculations

  1. Click the down arrow next to Dimensions in the data pane and select Create Parameter.
  2. In the Create Parameter dialog, do the following and click OK:
    • Name the parameter. In this example it is called "INPUT".
    • Choose Float for Data Type.
  3. Right-click [INPUT] in the data pane and select Show Parameter Control.
  4. Select Analysis > Create Calculated Field.
  5. In the Calculated Field dialog box that opens, do the following, and then click OK:
    • Name the calculated field. In this example, the calculated field is named "PREV".
    • In the formula field, create a calculation similar to the following:     

    {MAX({FIXED State: (IIF(SUM([Sales])<[INPUT],SUM([Sales]),NULL))})}

  6. Create a calculated field and name it for example "NEXT" with a calculation similar to the following:

    {MIN({FIXED State: (IIF(SUM([Sales])>=[INPUT],SUM([Sales]),NULL))})}

  7. Create a calculated field with a name such as "Rank Percentile" with a calculation similar to the following:

    RANK_PERCENTILE(SUM([Sales]))

  8. Create a calculated field with a name like "PREV%" with a calculation similar to the following:

    LOOKUP([Rank Percentile],-1)

  9. Create a calculated field "NEXT%" with a calculation similar to the following:

    [Rank Percentile]

  10. Create a calculated field and name lit for example  "PERCENTRANK" with a calculation similar to the following:
(([INPUT] - MIN([PREV]))
/
(MIN([NEXT]) - MIN([PREV])))
*
([NEXT%] - [PREV%])
+
[PREV%]

Building the view:

  1. Drag [State] from the Dimensions Pane to the Detail Shelf on the Marks Card.

  2. Right-click on [State] on the Marks Card and select Sort.

  3. In the Sort dialog, do the following and click OK:

    • For Sort Order, select "Ascending".
    • For Sort By, select "Field", Sales, and Sum.
  4. Drag [PERCENTRANK] from the Measures Pane to the Text Shelf on the Marks Card.

  5. Right-click on [PERCENTRANK] on the Marks Card and select Compute Using > State.

Additional Information

To display the percentile rank of values in a data set, use the built-in PERCENTILERANK() table calculation function.

Excel's PERCENTRANK function computes the percentile rank of a value NOT in the data set. Excel uses the following formula to interpolate the value:

Difference from X to previous value in array
/
Difference from next value in array to previous value in array
*
(rank percentile of next value in array - rank percentile of previous value in array)
+
rank percentile of previous value in array
 


Discuss this article... Feedback Forum
Did this article resolve the issue?