cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sum_body Frequent Visitor
Frequent Visitor

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 3
AnthonyTilley Established Member
Established Member

Re: Splitting results between two teams

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
AgentTeamSalesTarget
114050
223050
336050
442050
555050
614050
726050
839050
942050
1055050
11X5050

 

add the same agent twice as below

Table 2
AgentTeamSalesTarget
114050
223050
336050
442050
555050
614050
726050
839050
942050
1055050
1112525
1122525

 

 

if this is not possable then please show your sample data 

 

sum_body Frequent Visitor
Frequent Visitor

Re: Splitting results between two teams

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)

AgentStart date 1End date 1TeamStart date 2End date 2TeamStart date 3End date 3Team
101/01/201019/08/2019FranceN/AN/A N/AN/A 
201/01/201019/08/2019GermanyN/AN/A N/AN/A 
301/01/201019/08/2019UKN/AN/A N/AN/A 
401/01/201019/08/2019BelgiumN/AN/A N/AN/A 
501/01/201031/05/2019UK01/06/201930/06/2019France01/07/201919/08/2019West

(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 IDAgentCreated DateCreated MonthTeam
1A119/08/20198France
2A219/08/20198Germany
3A319/08/20198UK
4A419/08/20198Belgium
5A519/05/20195UK
6A119/08/20198France
7A219/08/20198Germany
8A319/08/20198UK
9A419/08/20198Belgium
10A501/05/20195UK
11A119/08/20198France
12A219/08/20198Germany
13A319/08/20198UK
14A419/08/20198Belgium
15A501/06/20196France
16A119/08/20198France
17A219/08/20198Germany
18A319/08/20198UK
19A419/08/20198Belgium
20A501/08/20198West

 

Team Target Lookup

 Q1Q2Q3Q4
France10111010
Germany20212021
UK10101010
Belgium20202020
West10101010

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 Established Member
Established Member

Re: Splitting results between two teams

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 

 

 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 123 members 1,621 guests
Please welcome our newest community members: