Hello, I have a data set with sales across several different cities. I am trying to put together a simple table that shows, for each city, 1 measure for how many combined sales the top 3 sales reps have, and then a 2nd measure for how many combined sales the next 3 sales reps have. Each city may have different reps that fall in the top 3.
To do this, I've been using the max function as such:
(Max(aggr(sum(Sales),Rep_Name,City),1)+(Max(aggr(sum(Sales),Rep_Name,City),2)+(Max(aggr(sum(Sales),Rep_Name,City),3) / sum(sales)
Unfortunately, this only works when there ARE at least 3 reps per city. In cities where there is only 1 or 2 reps, the measure shows up in the table as null instead, of the sum of the first 1-2 reps.
Is there a way to solve for this, so that if there isn't a 3rd, 4th, 5th rep, the sum of only the first N are shown?
Thank you in advance!!
P.S. if there are easier, more efficient ways to do this I would love to know!
