Average age with DIM and FACT

[Mark_Andersen]

Hi community

I have a problem calculate the average age per year and show it in a graph.

Thanks for the help in advance.

DIM_Employee

Employee ID	Age
1	40
2	50
3	45
4	60
5	30

FACT_Employee

Employee ID	Date active
1	01-10-2019
1	01-11-2019
2	01-12-2019
1	01-10-2020
2	01-11-2020
3	01-11-2020

Result november with page filter on month

2019 = 40 / 1 (count employee) = 40

2020 = 95 (50 + 45) / 2 (count employee) = 47,5

[Mark_Andersen · Accepted solution]

Thank you for your reply. 
The problem with sum('DIM_Employee'[Age]) is that it will sum employees with no match in FACT_Employee.

I need something like total sum DIM_Employee[age] if employee ID match with FACT_employee ID / Distinctcount(FACT_Employee[Employee ID)

[ryan_mayu]

@Mark_Andersen 

you can create a calendar table. Then create a measure

Measure = sum('DIM_Employee'[Age])/COUNTROWS(FACT_Employee)

please see the attachment.

[amitchandak]

@Mark_Andersen , Create a column in the table

year active= year([Date active])

Avg Age = divide(sum(Table[Age]), distinctcount(Fact[year Active]))