16 Replies Latest reply: Sep 18, 2018 9:29 AM by Ruben Marin

# region wise

Hi all,

i want to show in pivot table top5 customers by each region

i have 3 regions  --- so in pivot table i want to show  total 15 customers each region 5 customers.

how can i do this?

Thanks

Sony

• ###### Re: region wise

Hi,

try this

=If(Rank(Sum(Sales), 1, 1) <= 5, Sum(Sales))

Hope this helps

Thanks,

Pooja

• ###### Re: region wise

Try expression like:

If(Rank(Sum(Sales))<6, Sum(Sales))

• ###### Re: region wise

thanks  but  i  have to measures

i cant put sum(sales)

dimensions:

customer

satge

Measure

sum({<type={'service'}>amount)

sum({<type={'Harware'}>amount)

the above am using  how can i do this?

• ###### Re: region wise

Hi Sony, both answers (Pooja and Tresesco) seems to work. Just use your measure instead of sum(sales). That doesn't works?

• ###### Re: region wise

not working

it is showing for each region 15 customers

i

• ###### Re: region wise

I tried to reproduce it on Pooja's sample but only 5 customers per region are shown. Can you upload a sample or change the attached to show 15 customers by each region?

If(Rank(Sum(Sales), 4, 1) -> '4' to keep only one when there are different customers in the same rank.

• ###### Re: region wise

its not working in my case chk the attachment

Tresesco B

Pooja Nagaraj

Jyothish KC

Ruben Marin

• ###### Re: region wise

Hi sony, as Pooja saids below, if you have different expressions, those differenct expression are the ones showing other customers, try adding the same condition to all expresions. PFA.

You can check supress null values in dimension to remove the null value.

• ###### Re: region wise

sorry  ...

Ruben Marin

if i add customername in  the dimension it is giving me wronfg result..

check the attached.

actual requirment   region should not be in table

dim: accountname

salestage

• ###### Re: region wise

Hi Sony,

If you dont want to  modify the expression, then try this as dimension.

Replace customer with this expression:

=Aggr(If(Aggr(Rank(Sum(Sales),1,1),Region,Customer)<= 5,Customer),Region,Customer)

Exp: sum(Sales)

Br,

KC

• ###### Re: region wise

Also click on suppress null values.

But it is always recommended to modify the expressions rather using calculated dimensions. So the above mentioned solutions are more optimized

Br,

KC

• ###### Re: region wise

Yeah. It won't work when you have two expressions, If the top 5 customers are different for both the type, then it will display top5 for type=service and top 5 customers for type=Harware.

If you just want top 5 for each region you have to use expression like this:

=If(Rank(sum( {<Type={'service','Harware'}>}amount)) <= 5, sum( {<Type={'service','Harware'}>}amount))

Thanks,

Pooja

• ###### Re: region wise

Hi Sony,

Try this:

=If(Aggr(Rank(Sum(Value)),Region,Account_Name)<7,Sum(Value))

Also refer the attached.

Br,

KC

• ###### Re: region wise

but... i want to see the service amount and hardware amount as a measures also

• ###### Re: region wise

Just copy the same expression and replace you expression for service and hardware

Refer the attachment.

• ###### Re: region wise

Hi Sony, Jyothish answer above is working, isn't?