Create a Filtered Denominator for a Divide Function from one table in another table

[JJ_BSS]

I have two tables, Table 1 has Athletic team enrolments and Table 2 has full school enrolments I need to calculate participation on each team by school level and grade level which are fields in Table 2. 

My calculation should be take the sum of enrolments from Table 1 and then create the denominator filtering Table Two to count only students in Senior School (or Middle School or Junior School). Same concept for each grade level. 

I can manually input =COUNTA('AthleticEnrolment'[Enrolment])/504 to get the Participation rate for Senior School students on each team but the data tables are live and the denominator should change based on student withdrawals or new student arrivals. 

The School Level data is text type. The Filter functions I have been trying fail because I cannot use text and integer data in the formulas I have tried.

[v-jianpeng-msft · Accepted solution]

Hi, @JJ_BSS 

You can try using VAR, IN, DIVIDE DAX functions for calculations. Based on your description, I created the following data:

Find the ParticipationRate by establishing a measure similar to the following:

ParticipationRate = 
var _a=CALCULATE(SUM(Sheet1[Enrollment]))
var _b = CALCULATE(SUM(Sheet2[TotalEnrolment]),FILTER('Sheet2','Sheet2'[SchoolLevel] IN{"senior middle school","middle school"}))
RETURN DIVIDE(_a,_b)+0

Use filter to filter the categories in Table 2 where the School level is high school or junior high school. Then sum the TotalEnrolment in this table after filtering. You can also use the && symbol to combine more conditions, like:

FILTER('Sheet2','Sheet2'[SchoolLevel] IN{"senior middle school","middle school"}&&'Sheet2'[Grade]="grade 9")

This sum can be replaced by your COUNTA expression, using aggregate functions based on your data. Use the DIVIDE function at the end to calculate the ParticipationRate.

Use the newly created measure and related columns in the Table visual:

Measure will be calculated row by row based on the external filter Team Name, Grade and your Filter function. If the above suggestions are helpful to you, that would be great.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.