Forum Discussion
Splitting results between two teams
Hi All,
I'm new to Power BI but getting along pretty well (in my opinion) and finding the forum a great help!
However...I'm stuck and I'm hoping someone can tell me whether this is possible or not to solve simply.
I'm running reports for a Western European team, which is then sub-divided into 5 regions.
As is the nature of business, there's been some personel movemonts, and one agent is now supporting two regions. They've agreed to divide his results equally between the two teams (let's call them Team 1 and Team 2).
I've assigned this one agent to Team X, and have created two measures which then calculate the result for Team 1 and Team 2, by counting their own teams numbers and adding 1/2 of the result for Team X to each.
However...what I'm trying to build is a report for the teams to look at which will show their current performance against their target. I was originally putting in a slicer at the top for Teams 1-5, but that slicer would not include Team X results for team 1 & 2.
Any help would be much appreciated here. Just a note that the reason I'm doing this based on Team rather than agent is because agents have moved teams and I need to maintain their historical results, so in my data query I have a lookup table with start/end dates merged with the results table.
Thanks in advance
3 Replies
Do you have some sample data or preferably a sample pbix file.
for the best results i would not asign the user a diffrent tema and then split i would create two sets of data one for team 1 and one for team 2 each containg half of his total value that way it is actually like he is two people and is treated assuch so all other calculations should work fine
Example in stead of making the agent team x as belowin table 1
Table 1 Agent Team Sales Target 1 1 40 50 2 2 30 50 3 3 60 50 4 4 20 50 5 5 50 50 6 1 40 50 7 2 60 50 8 3 90 50 9 4 20 50 10 5 50 50 11 X 50 50 add the same agent twice as below
Table 2 Agent Team Sales Target 1 1 40 50 2 2 30 50 3 3 60 50 4 4 20 50 5 5 50 50 6 1 40 50 7 2 60 50 8 3 90 50 9 4 20 50 10 5 50 50 11 1 25 25 11 2 25 25 if this is not possable then please show your sample data
Hi Anthony, Thanks for replying so quickly! I've added below a simplified version of what I'm working with. (I can't see anywhere to upload the file but let me know if I've just missed something).
Agent Lookup (required for when agents move teams / departments)
Agent Start date 1 End date 1 Team Start date 2 End date 2 Team Start date 3 End date 3 Team 1 01/01/2010 19/08/2019 France N/A N/A N/A N/A 2 01/01/2010 19/08/2019 Germany N/A N/A N/A N/A 3 01/01/2010 19/08/2019 UK N/A N/A N/A N/A 4 01/01/2010 19/08/2019 Belgium N/A N/A N/A N/A 5 01/01/2010 31/05/2019 UK 01/06/2019 30/06/2019 France 01/07/2019 19/08/2019 West (West is the 'team' name that I've given to the agent who's numbers are split between France and Belgium)
Example Data (our target is based on quantity, so I'm just using a COUNT function on the Lead ID)
Lead ID Agent Created Date Created Month Team 1A 1 19/08/2019 8 France 2A 2 19/08/2019 8 Germany 3A 3 19/08/2019 8 UK 4A 4 19/08/2019 8 Belgium 5A 5 19/05/2019 5 UK 6A 1 19/08/2019 8 France 7A 2 19/08/2019 8 Germany 8A 3 19/08/2019 8 UK 9A 4 19/08/2019 8 Belgium 10A 5 01/05/2019 5 UK 11A 1 19/08/2019 8 France 12A 2 19/08/2019 8 Germany 13A 3 19/08/2019 8 UK 14A 4 19/08/2019 8 Belgium 15A 5 01/06/2019 6 France 16A 1 19/08/2019 8 France 17A 2 19/08/2019 8 Germany 18A 3 19/08/2019 8 UK 19A 4 19/08/2019 8 Belgium 20A 5 01/08/2019 8 West Team Target Lookup
Q1 Q2 Q3 Q4 France 10 11 10 10 Germany 20 21 20 21 UK 10 10 10 10 Belgium 20 20 20 20 West 10 10 10 10 Our targets are by team, they are not split down to each team member as their workload and opportunity is different. I've included the perculiarities of quarterly targets (some teams had annual targets which were not divisible by 4). This is a separate issue that I'm facing though so I can open a new thread if needed.
With your proposal, I'm not sure how I would populate team 1 and 2 for the agent, as it would need to be a 1:1 relationship between Agent and Lookup Table? In my current excel reports I manually filter and replace half the results for the agent, but that's what I'm trying to avoid in Power BI.
So far in Power BI:
1. Import Sales data (latest file from folder)
2. Import Agent Lookup (fixed excel table)
3. Merge both data and lookup
4. Use If function to add custom column (Team name) based on creation date and agent start/end date
5. Load data
Thanks,
Ben
To Share your file you need to save it to a cloud platform like Onedrive, Google drive or Drop box and then post a share link in the thread.
ill take a look at what you have provided but a full file would be best
