I have a table that looks somthing like this:
| Product | Sales | Salesperson |
| A | 100 | Peter |
| A | 200 | Peter |
| A | 300 | Paul |
| A | 700 | Paul |
| A | 10 | Alex |
| A | 20 | Alex |
| B | 10 | Peter |
| B | 20 | Peter |
| B | 300 | Paul |
| B | 300 | Paul |
| B | 700 | Alex |
| B | 450 | Alex |
So in this case Peter and Paul sold the most for product A and Alex and Paul for product B.
So what I want as a result would be
| Produkt | Average of Top 2 salespeople |
| A | 325 |
| B | 437,5 |
For the real data I build this expression but I just get a 0.
Sum(aggr(
sum(Sales),Product,
If(Aggr(Rank(Aggr(Sum([Sales]), [Product], [Salesperson]), 3, 1), [Product], [Salesperson]) <= 11, [Salesperson])
)
)
In this case it's for the top 10 salespeople.
Any help is appreciated! 🙂
