Here's something that comes close. Instead of using the Max Number for the chart, I modified the Expression using Set Analysis. The set finds the fifth highest Amount (Max(Amount, 4)) and then only displays those that are greater than or equal to that Amount.
With your data, that gets six Customers, but since there are two tied for fifth, this may be preferred.
Unfortunately, you can't do it by just limiting the number of visible rows in the presentation tab. (I wish it were that easy, but I've never been sucessful getting that to work.)
I have attached a solution that should work for you (it gets the right answer here...but may be a little harder to implement in a larger or more complicated app)
I used a calculated dimansion to limit the number of rows that were visible which corrects the total over the dimension. Be sure to check the 'Supress when value is null' on the dimension tab for the calculated dimension, or you are right back where you started.
Just noticed I got the same result as NMiller, but with a much morecomplicated approach. NMiller's is argueably more elegant.
As a side note, however, depending on how you want to handle ties in the ranking, the approach I offered would actually allow you a certain degree of flexibility by taking advangate of one of the optional parameters in the Rank function. From the Help section on the Rank() function:
The second parameter mode specifies the number representation of the function result.
mode
0 (default)
If all ranks within the sharing group fall on the low side of the middle value of the entire ranking, all rows get the lowest rank within the sharing group.
If all ranks within the sharing group fall on the high side of the middle value of the entire ranking, all rows get the highest rank within the sharing group.
If ranks within the sharing group span over the middle value of the entire ranking, all rows get the value corresponding to the average of the top and bottom ranking in the entire column segment.
1
Lowest rank on all rows.
2
Average rank on all rows.
3
Highest rank on all rows.
4
Lowest rank on first row, then incremented by one for each row.
Try this expression: if(rank(sum(Amount))<=5, sum(Amount)) And, use sum of rows as total mode. Or add aggr, so you cvan use mode expression total sum(aggr(if(rank(sum(Amount))<=5, sum(Amount)), Customer))
I have the same scenario, I have to select the top 15 customers and calculate the revenue share based on the total of only top 15 customers revenue total.
I am able to calculate it based on the total revenue however unable to calcualte it based on the top15 customers, any pointers to this will be appreciated.
