Splitting results between two teams

[sum_body]

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

[AnthonyTilley]

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 

[sum_body]

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

[AnthonyTilley]

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