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
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.
Super UserGotcha! 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
Maybe, but google drive is block on company network. Can't even access my own.
