KNOWLEDGE BASE

## How to group by rank of percentile and count the items

Published: 26 Sep 2022

### Question

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

### Environment

• Tableau Desktop
• Windows 10
• Excel

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.