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

# Calculate Percentage of Top N to Total

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

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

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

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?

• ###### 1. Re: Calculate Percentage of Top N to Total

Hope this helps.

Example 1

Example 2

Example 3

Steps.

Change filters to context

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Calculate Percentage of Top N to Total

Thank you Shin! Looks like we are almost there. Your solution worked great for Example 1 and 2 but is not returning the values I expected for Example 2. For Type = Owned the calculations are returning the following: Top N Count % is returning 29% vs. 71%, and Top N Sales % is returning 39% vs. 76%. It looks like the ranking might be capturing the top 2 employees across all managers versus the top 2 employees for each manager. Not sure what I need to tweak to adjust this.

• ###### 3. Re: Calculate Percentage of Top N to Total

Here you go.

Both Top N Count % and Top n SALES %, TABLE Calc's "Rank Sales" need to be modified as above.

If this helped, please mark my answer as correct to close the thread, not from inbox but from original post.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 4. Re: Calculate Percentage of Top N to Total

Shin you are awesome! Thank you so much. I have been banging my head against the wall for a few days trying to figured this out.