How to group by rank of percentile and count the items

發佈時間 : 26 Sep 2022
上次修改日期 : 27 Sep 2022

問題

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

環境

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.
before