5 Replies Latest reply on Feb 6, 2019 3:02 AM by Jim Dehner

# Top N% Sales contributed by X users

Hi,

I have data looks like the attached example. and I need to create a text table on a new sheet to show the following information. Please help!

 Top N% Sales # of Users Top 20% \$931,013 6 Top 50% \$2,430,172 19 Top 80% \$3,987,965 35 All - 100% \$4,998,577 100
• ###### 1. Re: Top N% Sales contributed by X users

Hi

This is a complex analysis that uses a number of table calculations to return what you want

I have attached a superstore example that I use in presentations - the example is sales quintiles

It returns this

You can reverse engineer it starting with the detail sheet - the final summary has a lot of hidden rows that are needed for the table calculations

Once you get into it let me know if there are questions

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 2. Re: Top N% Sales contributed by X users

Hi Jim,

Thanks for looking into it. I am stuck on creating calculation fields "rank cust name%" and "relative sales level". It looks like you've fixed the order date by year. What if I want to make the range of dates dynamic so that interactors can change filters to "This quarter", "Last 2 quarters", "This year", or "10/1/2018-11/1/2018"  for example?

• ###### 3. Re: Top N% Sales contributed by X users

I hope  you find a solution to that - rank and dynamic are not friends -

the problem you will run into is you need to be able to find the running totals by customer (or whatever) and then compare it to a percent of total for the same period - and that ends up being a table where you can sum sales and count customer

You change the period (dynamically) and you change the table (the customer order and the break points)   - I played with parameters but in the end you are creating another table -

Jim

1 of 1 people found this helpful
• ###### 4. Re: Top N% Sales contributed by X users

Hi Jim,

Thanks! That's what I thought but was hoping I was wrong . I'll still mark the 1st reply as the correct answer since it's very helpful!

• ###### 5. Re: Top N% Sales contributed by X users

Thanks - I almost hate to mention this because itt is a lot of work but you could do separate worksheets by year then do a sheet swap on a dashboard - in any event good luck

Jim