How to group by rank of percentile and count the items

Publicado: 26 Sep 2022
Data da última modificação: 27 Sep 2022

Pergunta

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

Ambiente

Tableau Desktop
Windows 10
Excel

Resposta

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