Top N in Pivot table multiple dimension

[alanwong1178]

Hi All,

I would like to have a pivot table with two dimension: 1) Client 2)Region which shows the top 3  region for each client based on the profit as requirement below: 

I tried to write something like: 

aggr(if(rank(SUM(Profit))<=3,Region),Region)

It does not work as it is based on the ranking of the total profit of region rather than the ranking of profit of region for each individual client. This gives me some weird result

Weird result:

Ideal Result:

[abhijitnalekar]

Hi @alanwong1178 ,

Please update your formula like below.

=if(aggr(rank(Sum(Profit),Client),Client,Region ) <=3, Region)

[NitinK7]

try to add calculated dimension for region

Dimension:

Client 

=Aggr(If(Aggr(Rank(Sum(profit))<=3,Client,Region), Region), Client,Region )

Measure

Sum(Sales)

Sum(profit)

[abhijitnalekar]

Hi @alanwong1178 ,

Please update expression as below

=Aggr(If(Aggr(Rank(Sum(Profit),Client,Region)<=3,Client,Region), Region), Client,Region )