2 Replies Latest reply: Dec 13, 2012 6:48 PM by debarrow RSS

    Average sales per partner for Top N customers

      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

       

      StateViewing Partner SalesTop 20 Partner Avg Sales
      Texas   $850,000            $1,000,000
      California   $750,000            $450,000
      New York   $650,000            $724,000
        • Re: Average sales per partner for Top N customers

          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