How to group by rank of percentile and count the items

Published: 26 Sep 2022
Last Modified Date: 27 Sep 2022

Question

How to group data by percentile rank and count the items in each group.

Environment

Tableau Desktop
Windows 10
Excel

Answer

1. Create several fields like below.
1) name: RANK_PERCENTILE(Sales)
value:
IF RANK_PERCENTILE(SUM([Sales])) <= 0.1 THEN "10"
ELSEIF RANK_PERCENTILE(SUM([Sales])) <= 0.2 THEN "9"
ELSEIF RANK_PERCENTILE(SUM([Sales])) <= 0.3 THEN "8"
ELSEIF RANK_PERCENTILE(SUM([Sales])) <= 0.4 THEN "7"
ELSEIF RANK_PERCENTILE(SUM([Sales])) <= 0.5 THEN "6"
ELSEIF RANK_PERCENTILE(SUM([Sales])) <= 0.6 THEN "5"
ELSEIF RANK_PERCENTILE(SUM([Sales])) <= 0.7 THEN "4"
ELSEIF RANK_PERCENTILE(SUM([Sales])) <= 0.8 THEN "3"
ELSEIF RANK_PERCENTILE(SUM([Sales])) <= 0.9 THEN "2"
ELSEIF RANK_PERCENTILE(SUM([Sales])) <= 1.0 THEN "1"
ELSE NULL END
//group by rank of percentile
2) name: count
value:
if first()=1 then 1
elseif
lookup([RANK_PERCENTILE(Sales)],0)=lookup([RANK_PERCENTILE(Sales)],-1)
then 1+
PREVIOUS_VALUE(0)
else 1
end
//count the items in each group
3)name: filter
value:
if last()=0 or
lookup( [RANK_PERCENTILE(Sales)],0) <> lookup([RANK_PERCENTILE(Sales)],1) then "break" end

2. Drag [customer name] and [RANK_PERCENTILE(Sales)] to the rows.
3. Drag [filter] to the filter card, select the item [break], and click OK.
4. Drag [count] to the text card.
see the attachment file below.
before