Calculating median on an aggregate value

[#1]

I have a data set that includes sales by sales rep. I need to find out what the median sale total is by rep. Whenever I use the median equation it gives me the median sale value, not the median sale value by rep. Can someone help me with this. I am sure it is something simple that I am missing.

Thanks

Naomi

[johnw]

I believe this:

median(aggr(sum(Sales),SalesRep))

Assuming I got that right, it says to sum the sales by sales rep, and then take the median of those sums.

[#3]

This works, but when I try to put set analysis in, it stops working. This is my equation

=median({1 <[Fiscal Year] = {2012}, [Partner Country] = {'United States','Canada'}, Won = {1}, RegSold = {1}, DateFlag = {'Closed'}>} aggr( sum([Calculated Sold Value SRP]),[Partner Rep]))

I am trying to make sure that it always gives us the median for this fiscal year, for only US & CA, for those that are Closed regardless of the parameters that are selected. I think that the {1 < should do that, but it isn't. If I select these parameters from my lists, then I get the right number, but I want it to calculate without making the selection. Do you see something that I entered wrong?

[johnw · Accepted solution]

The set needs to be applied both places:

median({your set} aggr(sum({your set} Sales),SalesRep))

[#5]

That worked!!! Thank you so much!

[tahernia_p]

Thanks for your great formula, it works for my problem too