Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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?