Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this:
Sum(Aggr(If(Rank(Sum(Revenue), 4, 1) < 6, 1, 0), Product, Client))
May be this:
Sum(Aggr(If(Rank(Sum(Revenue), 4, 1) < 6, 1, 0), Product, Client))
maybe
sum(TOTAL <Client> if(aggr(rank(sum(Revenue), 4, 1), Product, Client) <= 5, 1))
Thank you! This works great. Both yours and Grossi's formula give me exactly what I need.
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.