Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Hi @alanwong1178 ,
Please update your formula like below.
=if(aggr(rank(Sum(Profit),Client),Client,Region ) <=3, Region)
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)
Hi @alanwong1178 ,
Please update expression as below
=Aggr(If(Aggr(Rank(Sum(Profit),Client,Region)<=3,Client,Region), Region), Client,Region )