Forum Discussion
Staff Sharing Sales
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
Super UserHi! 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
Thanks for sharing that idea audreygerred, I will give it a try.
I have been trying to avoid bi-directional relationship, however in this case it might be unavoidable. Will let you know how it goes. The sample file you've uploaded, thanks for that I will have to access out of my work network, restrictions don't allow me to get it.
btw, I do have a CalendarDIM, thanks for mentioning it.
Cheers
Adrian- gmsamborn
- audreygerred
Woops! Should be good now. Thanks for the catch! https://drive.google.com/file/d/1pNDJ5fHvEPRfhBonbro_ZgRVJziEG0b9/view?usp=sharing
