Count Products that a Client is a top 5 Revenue generator in

[#1]

Hi,

I have a flat table with 3 fields: Client, Product, Revenue, that looks like this.

Client	Product	Revenue
A	X	$10
A	Y	$15
A	Z	$8
B	X	$9
B	Y	$12
C	X	$9
C	Y	$5
C	W	$3
…	…	…

I need to have find a formula that return the number of products that client A is a top 5 revenue generators in. For example, if A is rank #2 in X, #5 in Y and #7 in Z in term of revenue, the formula will return "2", as there are 2 products in which A is a top 5 revenue generator.

I manage to use to create a table that tell you the revenue rank in each of the Product for client A:

- Current Selection: Client A

- Dimension: Product

- Measure: aggr(rank(sum(Revenue), 4, 1), Product, Client)

We can easily read off the table to see how many products in which A is a top 5. But is there a way to put it into a formula

Thanks,

Tong

[sunny_talwar · Accepted solution]

May be this:

Sum(Aggr(If(Rank(Sum(Revenue), 4, 1) < 6, 1, 0), Product, Client))

[maxgro]

maybe

sum(TOTAL <Client> if(aggr(rank(sum(Revenue), 4, 1), Product, Client) <= 5, 1))

[#4]

Thank you! This works great. Both yours and Grossi's formula give me exactly what I need.

[#5]

Thanks! Your formula works as well. But I think Talwar's is a little more flexible as it doesn't use Total modifier. Since I'm also thinking of using the formula in a table or combo chart, Total modifier may affect the result.