Forum Discussion
Staff Sharing Sales
Hello, any help or ideas is appreciated.
I have a group of sales staff who share sales through what we call Rep Code. Each is assigned Rep Codes and some are shared.
The sales table has records of sales that identify which record is attributed back to the sales staff via Rep Code.
Problem I have is that each sales staff gets 100% attribution even if they share the Rep Code. This is for calculating their incentive for the sale.
I have tried grouping, tried creating a bridge table and using treatas with no satisfactory result. I need to show the total sales for each for all the Rep Codes an individual may have or share.
Here is a sample of my tables for Staff and Sales:
| Staff Name | Rep Code |
| Eliott | EGRE |
| Eliott | GTEG |
| Eliott | HCEG |
| Eliott | JHEG |
| Eliott | NNEG |
| Eliott | RMEG |
| Johnny | GTEG |
| Johnny | GTES |
| Johnny | VANC |
| Jordan | EGRE |
| Jordan | GTEG |
| Jordan | HCEG |
| Jordan | JHEG |
| Jordan | JHNS |
| Jordan | JHOP |
| Nazlin | JHNS |
| Nazlin | NSUN |
| Nazlin | RMNS |
| Robert | NSUN |
| Robert | RMEG |
| Robert | RMEH |
| Robert | RMNS |
| Effective Date | Rep Code | Amount |
| 12/29/2023 | NSUN | 99861.40 |
| 12/29/2023 | EGRE | 99707.14 |
| 12/29/2023 | JHOP | 99682.33 |
| 12/29/2023 | RMEG | 99436.98 |
| 12/29/2023 | NSUN | 99422.18 |
| 12/29/2023 | JHOP | 99356.25 |
| 12/29/2023 | GTEG | 99287.20 |
| 12/29/2023 | EGRE | 99041.38 |
| 12/29/2023 | EGRE | 990159.13 |
| 12/29/2023 | EGRE | 98809.19 |
| 12/29/2023 | GTEG | 98782.04 |
| 12/29/2023 | EGRE | 98647.74 |
Gotcha! OK - since that is what you want - here is how I did it... I took your Staff table and added that - then from that I created a dim table with just the Staff name and a dim table of just the rep codes. I then added in your Sales table. I then joined the Staff name dim to Staff table and the Rep Code dim to both the Staff (with a bi-directional join, which should be avoided, but it is needed here) and the Sales table (you would also need a date dim table so you could filter and stuff by dates, but I didn't do that here just to demo). In the visual, I put Staff name from the Staff Dim table and Rep code from the Rep Code dim table and the measure of Sales that I made using the sum of sales from the sales table. Here's the pbix I made: adrian_file
8 Replies
- Khushidesai0109
Skilled Sharer
Hii Anonymous
Step 1: Create a Calculated Column in the Staff TableStaff Count =
VAR CurrentRepCode = Sales[Rep Code]
RETURN
CALCULATE(
DISTINCTCOUNT(Staff[Staff Name]),
Staff[Rep Code] = CurrentRepCode
)Step 2: Create a Measure for Adjusted Sales Attribution
Sales Attributed =
VAR RepCodeCount =
CALCULATE(
DISTINCTCOUNT(Staff[Staff Name]),
Staff[Rep Code] = SELECTEDVALUE(Sales[Rep Code])
)RETURN
SUM(Sales[Amount]) / RepCodeCountIf this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution! - audreygerred
Super User
Hi! Do you want it where each Sales Rep would show they Rep Codes associated with them and the total amount (i.e. anyone who shares that rep code shows that same amount), like below?
audreygerred Thanks for taking a look at this.
Yes that's what I am after. The report eventually will be using RLS to allow individuals to view their own totals. For example if Eliott viewed the report he/she will see:Eliott EGRE 1,386,364.58 Eliott GTEG 198,069.24 Eliott RMEG 99,463.98 Once this is achieved there is some underlying calculations to get here and then to the incentive which I have yet to get to. If I can help on this would be a big win for me as a base to start with the rest of the calculations.
- audreygerred
Gotcha! OK - since that is what you want - here is how I did it... I took your Staff table and added that - then from that I created a dim table with just the Staff name and a dim table of just the rep codes. I then added in your Sales table. I then joined the Staff name dim to Staff table and the Rep Code dim to both the Staff (with a bi-directional join, which should be avoided, but it is needed here) and the Sales table (you would also need a date dim table so you could filter and stuff by dates, but I didn't do that here just to demo). In the visual, I put Staff name from the Staff Dim table and Rep code from the Rep Code dim table and the measure of Sales that I made using the sum of sales from the sales table. Here's the pbix I made: adrian_file
