Forum Discussion
Average age with DIM and FACT
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
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)
3 Replies
- amitchandak
Super User
Mark_Andersen , Create a column in the table
year active= year([Date active])
Avg Age = divide(sum(Table[Age]), distinctcount(Fact[year Active]))
ryan_mayuyou can create a calendar table. Then create a measure
Measure = sum('DIM_Employee'[Age])/COUNTROWS(FACT_Employee)please see the attachment.
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)
