I have one table (Residency) that looks like the following:
| Person | Resident |
|---|---|
| 1852 | 1 |
| 1863 | 1 |
| 1963 | 0 |
I’m currently calculating the percent who are residents using the following:
Per Housing First Year = DIVIDE(CALCULATE(COUNT(Sheet1[First_Fall_Term_Res]), Sheet1[First_Fall_Term_Res] = 1), COUNT(Sheet1[First_Fall_Term_Res]))
I have another table (Athletes) like the following:
| ID | Athlete |
|---|---|
| 1852 | Yes |
| 1863 | No |
| 1963 | Yes |
I have these tables joined on Person and ID. I would like to calculate the Per Housing First Year who are Athletes. How can I build out a formula that allows me to filter on the Athlete column in another table? I’ve used CALCUALTE to set filter conditions within the same table. For example if the Athlete column was on the same table I could do the following:
Per Housing First Year = DIVIDE(CALCULATE(COUNT(Sheet1[First_Fall_Term_Res]), Sheet1[First_Fall_Term_Res] = 1, Sheet1[Athlete] = “Yes”), COUNT(Sheet1[First_Fall_Term_Res]))
But how can I reference another table?