Caclulate sum of top 20

[petermoyaert]

I have to prepare a table with dimension Account Managers.

I need to caclulate for each Account Manager the sum of his top 20 customers (calculated column 1)

Then I need to calculate the result of this exercise with total sales of each account manager (calculated column 2)

Anyone who can give me the clue for this ?

[luciancotea]

See Rank()

rank( sum( Sales )) gives you rank in top

You will need to combine with sum(aggr( 'expr', [Account Manager] )) to get Top 20 sum for each Manager

[satishkurra]

Hi

The below expression gives you the original rank

=avg(aggr(rank( sum( {$<AccountManager=>} Sales) ), AccountManager))

The below gives the top 20

=if(avg(aggr(rank( sum( {$<AccountManager=>} Sales) ), AccountManager)) <= 20, AccountManager)

Thanks

Satish

[swuehl]

Maybe like this (in a table with dimension [Account Manager]):

Expression1:

=Sum( Aggr( If( Rank( Sum(Sales))<= 20, Sum(Sales) ), [Account Manager], [Customer]))

Expression2:

=Sum(Sales)

[haven't fully understood what you mean with 'calculate the result of this exercise with total sales of each account manager']

[petermoyaert]

Perfect,

However, some customers are visited by multiple accountmanagers.

I think that this formula takes in deed the top 20 clients, but takes in consideration total sales of these customers, so including the sales by other account managers.

Any idea how to solve ?

[swuehl]

It should only take the sales of the customer into account that relates to the  account manager.

If you get not the requested result, could you upload some sample data or even better a small sample QVW?