4 Replies Latest reply on Nov 8, 2018 2:42 PM by Randolph Ralph

    Calculate Percentage of Top N to Total

    Randolph Ralph

      I have a Dashboard that allows the Top N Ranked Sales Employees based on 2018 Sales. I have parameters that filter based on Type, Location Code, or Manager (only one of which should be filtered). I am attempting to add two calculations to the dashboard in order to answer two questions.

       

      1. What percent of Top N Ranked Sales Employees make up the Total Sales Employees for the Type, Location Code, or Manager (parameters)?

       

      2. The Top N Ranked Sales Employees 2018 Sales account for what percentage of Total Sales for the Type, Location Code, or Manager (parameters)?

       

      Example 1: Manager = Isabel Cruz

      Expected Calculation

      1. 40% =  2 Sales Employee (Top N) / 5 Total Sales Employees in 2018 for Isabel Cruz

      2. 47% = $520 2018 Sales from Top N Ranked Sales Employee / $1110 2018 Total Sales for Isabel Cruz

      Ex1.jpg

       

      Example 2: Type = Owned

      Expected Calculation

      1. 71% =  5 Sales Employee (Top N) / 7 Total Sales Employees in 2018 for Owned

      2. 76% = $750 2018 Sales from Top N Ranked Sales Employee / $990 2018 Total Sales for Owned

      Ex2.jpg

       

      Example 3: Location Code = BC1

      Expected Calculation

      1. 100% =  1 Sales Employee (Top N) / 1 Total Sales Employees in for BC1

      2. 100% = $110 2018 Sales from Top N Ranked Sales Employee / $110 2018Total Sales for BC1

      Ex3.jpg

       

      I have attempted to create the calculated fields to perform the calculations as shown above, but not sure how to apply the Top N filter to get the Top N totals compared to the Total based on Type, Location Code, or Manager.

       

      Any idea if this can be accomplished or recommendations of how I could accomplish this?