Employee production by team production

[vdlbrtt]

Hi guys, im trying to get this results in powerbi, i need to get employee performance like this 

it dont matter if is in a table or in a matrix, i just need to detail the employee production by day and the average of their team in the same day, in the end i need to show the totals as average by employee and by the team

so the performance will be the employee production divide by team production 

these are the table i have

Production Table

DATE	NAME	TEAM	PRODUCTION
3-mai	Employee 1	Team 1	1
3-mai	Employee 2	Team 2	5
3-mai	Employee 3	Team 2	5
3-mai	Employee 4	Team 1	2
3-mai	Employee 5	Team 3	3
3-mai	Employee 6	Team 3	1
2-mai	Employee 1	Team 1	2
2-mai	Employee 2	Team 2	5
2-mai	Employee 3	Team 2	5
2-mai	Employee 4	Team 1	5
2-mai	Employee 5	Team 3	4
2-mai	Employee 6	Team 3	3

Calendar Table

MONTH	MONTH NUMBER	WEEK
3-mai	5	4
4-mai	5	5
5-mai	5	6
6-mai	5	7
7-mai	5	1
8-mai	5	2
9-mai	5	3
10-mai	5	4
11-mai	5	5
12-mai	5	6
13-mai	5	7
14-mai	5	1

Employee name table

NAME	ID
Employee 1	157
Employee 2	639
Employee 3	918
Employee 4	898
Employee 5	802
Employee 6	167
Employee 7	259
Employee 8	830
Employee 9	977
Employee 10	447
Employee 11	660

[lbendlin]

Your production table only covers two days and your Employee Name table can be omitted. Please provide more production sample data.

[vdlbrtt]

Hi, thank you for the attention, i tried some things but nothing works, here are some statements i need to consider

• Performance is: Employee Production divided by Queue Average Production, so it will give me the percentage if employee is 90% 110%.... above ou below the others

• Some employees can be in multiple teams, but they compete just with the employees in the same queue

• For Individual Production and Queue Average Production must not consider the days the selected employee were absent

I got this measure for the employee production, but it

• Dont work with employees that are in two tables, Team1Production and Team2Production, it needs to sum all their production

• Cant get the average of the employees in the same queue, it always takes the average of ALL queues and it needs to not consider the absent
  
  

Here is the link wetransfer link 

Individual Production = 
VAR _Employee = SELECTEDVALUE('factEmployees'[Names])

VAR DatesToExclude =
    FILTER(
        SUMMARIZE(
            FILTER('Absence', 'Absence'[Name] = _Employee),
            'Absence'[Date]
        ),
        'Absence'[Date] <> BLANK()
    )

VAR AvgTeam1 =
    AVERAGEX(
        KEEPFILTERS(VALUES('factCalendar'[Date])),
        CALCULATE(
            AVERAGE(Team1Production[Production]),
            FILTER(
                'factCalendar',
                NOT('factCalendar'[Date] IN DatesToExclude)
            )
        )
    )

    VAR AvgTeam2 =
    AVERAGEX(
        KEEPFILTERS(VALUES('factCalendar'[Date])),
        CALCULATE(
            AVERAGE(Team2Production[Production]),
            FILTER(
                'factCalendar',
                NOT('factCalendar'[Date] IN DatesToExclude)
            )
        )
    )

RETURN
AvgTeam1+AvgTeam2