Hi,
I have a simple report. I want to rank the top 5 vendors by order qty, as well as show the overall average for all vendors.
To get my top 5, I have a detailed filter on Rank < 6 set to before aggregation. The problem is this filter gets processed before the overall average gets computed, so my overall average is based on those top 5 vendors, not the entire vendor population.
So I tried the 'after aggregation' option, and I get all rankings, and my overall average is based on all vendors.
What am I doing wrong? Do I need to do something on the Rank function, like prefilter? How does the prefilter option work anyways?
Hi,
The way I see it, you have two approaches you could take to achieve this.
One is to calculate the overall average in a different query, then UNION this to your query containing your top 5 vendors, and base your report on the results of the UNION.
The other is to add a calculation to your report with an expression like: if ([Rank] <=5) then ('Best 5') else ('Others')
Include this calculation in the Properties property of your list, then conditionally hide the rows where the calculation is equal to 'Others'
Good luck!
MF.