Hello! Hope everyone answering this or referencing this in the future is having a great day!
I have a problem which I will describe below!
In one visual, I am trying to rank customers based on 22 commissionable sales by each salesperson in the company. In my current dashboard where the ranking calc is needed, we are looking at every salesperson in the company across all BU's. The first few columns are: Division, Sub-Division, Cons #, Consultant, Cust #, Cust Name, Ship To #, Ship To Name, 7-8 columns of item data, some other sales data , '22 commissionable Sales, '21 Margin Cost, '22 Margin Cost. I want to base the rank off of '22 commissionable sales, By Customer, By Rep.
We have a Customer table containing our customer data (name, #, Contract, GPO, etc...), a sales table with the commissionable sales, Cons #, and Cons Name, and explicit calculations in our expression tables that replicate all of the values in our tables so we can use the values without writing SUM(..) a million times.
I'm struggling with how to give context to the Customer # across a total of commissionable sales for that customer compared to the consultants other customers. Ideally, I'd like the first few columns to look something like what is below, obviously with item data, contract data, other sales data, and '22 commissionable sales data out to the right a bit!
| Division | Sub-Div | Cons # | Cons Name | Cust # | Cust Name | Rank |
| HC | | 1 | Al | 2342 | Lions | 4 |
| HC | | 2 | Tim | 523234 | Tigers | 16 |
| Home | | 78 | Bertha | 25435234 | Bears | 3 |
Home | | 78 | Bertha | 1234235 | Oh My! | 10 |
| HC | | 3 | Gertrude | 86786 | Jokes inc | 4 |
| HC | | 2 | Tim | 4658 | Not Tigers | 2 |
| HC | | 1 | Al | 76824 | Another One | 1 |
| HC | | 1 | Al | 2342 | Lions | 4 |
| HC | | 2 | Tim | 4658 | Not Tigers | 2 |
Last Request!! I would love for the calculation to show maybe Ranks 1-20 and the have a line for Other, where the row would include the "rest" of the customers that maybe wouldn't be included in the top 20. This would be super nice and if there was a way I could remap our dashboard to make this work, I would happily do so! Even if the dashboard would need to only display one salesperson at a time (with a drop down menu that would allow me to select a different salesperson if wanted or needed), the "other" sub-total of all non top 20 customers for each salesperson would be very helpful.
Model changes are possible but our refresh hits at 6am and would probably need to put any changes into a UAT enviro for at least a day. Possible, but not ideal. Looking forward to seeing what the community produces!!
