Average sales per partner for Top N customers

[#1]

i have a table where I caclulate the current year sales for the partner by State

Dimension (row) = State

first Expression (Column)  is sum of sales for 2012 for the selected partner

in the 2nd expression (column) for each state I want to show the average sales per partner for that state.

So if, Texas's top 20 partners had sales of 20,000,000 in total, the value for texas in the second column would be $1,000,000.

It should only ignore the partner selection but change based on other user filters in Current Selections.

I am assuming I need to use the aggr(rank(sum()) function divided by the TopN value (# of Partners)

Sample of results

State	Viewing Partner Sales	Top 20 Partner Avg Sales
Texas	$850,000	$1,000,000
California	$750,000	$450,000
New York	$650,000	$724,000

[#2]

this appears to work but I am sure there is a consolidated version of this formula.  Uses the FirstSortedvalue() function to return top 1 thru 10 partner sales for the current year.  then simply divides by 10.   it would be replaced with a variable so the partner can compare to top 20, etc...

=(

FirstSortedValue(aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),[VAR Reporting Name]),

-aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),

[VAR Reporting Name]),1)

+

FirstSortedValue(aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),[VAR Reporting Name]),

-aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),

[VAR Reporting Name]),2)

+

FirstSortedValue(aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),[VAR Reporting Name]),

-aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),

[VAR Reporting Name]),3)

+

FirstSortedValue(aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),[VAR Reporting Name]),

-aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),

[VAR Reporting Name]),4)

+

FirstSortedValue(aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),[VAR Reporting Name]),

-aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),

[VAR Reporting Name]),5)

+

FirstSortedValue(aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),[VAR Reporting Name]),

-aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),

[VAR Reporting Name]),6)

+

FirstSortedValue(aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),[VAR Reporting Name]),

-aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),

[VAR Reporting Name]),7)

+

FirstSortedValue(aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),[VAR Reporting Name]),

-aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),

[VAR Reporting Name]),8)

+

FirstSortedValue(aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),[VAR Reporting Name]),

-aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),

[VAR Reporting Name]),9)

+

FirstSortedValue(aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),[VAR Reporting Name]),

-aggr(sum({<[Calendar Year]={'$(vMaxInvoiceYear)'}>}[Total Sales]),

[VAR Reporting Name]),10)



)

/10



/1000

[#3]

ONe issue I notice is that the State Row average changes when I filter by a state.

example,

Texas state average is 16k when I do not select Texas

when I do select Tx and the table reduces to one row for Texas, the avg increases to 19k.  I am not sure why.  Any ideas?