How to Build Measure to find Weekly Average Per Employ

[kbrewer]

I need to build a measure to show the average production each employee has per week. My raw data doesn't use numbers it uses the number of occurrences of a specific phase (column cschcode) in a specific week to count how many each employee (Column cforename) has completed per week.  Bellow is the raw data example along with the table I have created so far to show weekly production. 3rd picture shows what the table is using from the data. I am very new to power BI so if anyone could help me I would appreciate it. 

[v-yuezhe-msft]

@kbrewer,

Do you have date column in the PRODUCTION_SCH table and use the date field to create relationship with Date table? If so, please create the Weeknnum column and Average measure in PORDUCTION_SCH table.

Weeknum = WEEKNUM(PRODUCTION_SCH[Date])

Average = CALCULATE(COUNT(PRODUCTION_SCH[cschcode]),ALLEXCEPT(PRODUCTION_SCH,PRODUCTION_SCH[cforename]))/CALCULATE(MAX(PRODUCTION_SCH[Weeknum]),ALL(PRODUCTION_SCH))

If the above DAX don't help, please post expected result and explain the logic you use to calculate Average per Employ.

Regards,
Lydia

[kbrewer]

@v-yuezhe-msft

I have my "week number" calculating in my Date table ( shown below) and yes I have a relationship between that column and a date column in my Production_sch table. Can I use this instead of making another column on the Production_SCH table? Production_sch table is raw data that a pull weekly from another source. I dont want to change that data in any way by adding a column because I want to keep the data in its raw form so it matches every time I export the data each week. 

 This is what I tried : 

Average = CALCULATE(COUNT(PRODUCTION_SCH[cschcode]),ALLEXCEPT(PRODUCTION_SCH,PRODUCTION_SCH[cforename]))/CALCULATE(MAX('DATE'[Weeknum]),ALL(PRODUCTION_SCH))

It looks like it is close to what i am looking for but not exactly  ( the Average measure i tried is in the 2nd picture ) . i want to calculate the average worked per week but only average the amount of weeks worked. ex: so far my table goes to week 43 so abdon worked 42 out of 43 weeks so his average should be over 42 weeks, and when he works next week it will be average over 43 weeks and so on... Alan G has worked 22 weeks out of 43 so his average should be over 22 weeks and add from there as he works more weeks. ( photo below hows the table with these employees numbers)  

my calculations show that these 4 employees should have the following averages:

Abdon: 6.90

Abelardo: 3.49

Alan G: 1.05

Alexander: 1

[kbrewer]

is there any other way? i dont want to add anything to the Production_Sch table because i want to keep it in its raw data form since i pull a report weekly to export this raw data. 

[v-yuezhe-msft]

@kbrewer,

It is not possible to calculate the week number of each employee in date table, it should be calculated based on the weeknum column of the Prodection_Sch table.

Regards,
Lydia

[v-yuezhe-msft · Accepted solution]

@kbrewer,

You would need to create weeknum column in the PRODUCTION_SCH table, and use the DAX below to calculate Average.

Average  = CALCULATE(COUNT(PRODUCTION_SCH[cschcode]),ALLEXCEPT(PRODUCTION_SCH,PRODUCTION_SCH[cforename]))/CALCULATE(DISTINCTCOUNT(PRODUCTION_SCH[Weeknum]))

Regards,
Lydia