CALCULATE and filtering by aggregate comparison

[Gutenvirt]

I'm new to Power Bi and DAX but I have some familarity with Excel and Tableau.  

In any event, I'd like to use the CALCULATE function to find the average of some column if the the average of another column is equal to the average of another column.

I tried:

InRange= CALCULATE(AVERAGE(tbl2[percent]),AVERAGE(tbl2[num_partic])=AVERAGE(tbl_input[num_build]))

It gives an error about True/False expressions (which I'd assume means it can equate two aggregate functions)

Ideally I'd have a predetermined tolerance of about +/- 15% but I can work on that if I get the above working.  Any  help woudl be greatly appreciated!

[Greg_Deckler]

At first I thought you might need a filter clause but now I can't quite wrap my head around what you are trying to do and I think it is because of the relationships perhaps. tbl2 must be related to tbl_input, correct? This is a measure, correct? What happens if the averages don't equal one another? Can you just use an IF statement:

IF(AVERAGE(tbl2[num_partic])=AVERAGE(tbl_input[num_build]),AVERAGE(tbl2[percent]),"FALSE")

?

[Gutenvirt]

Sorry, I should have provided a bit of context.  I'm looking at an NCEA dataset about school districts.  Here is a sample of the data:

district	rndstratio	num_schs	clsrm_tch_fte	num_stds
Alachua	16	72	1804	28157
Baker	16	9	312	4985
Bay	15	51	1794	27053
Bradford	13	12	243	3239
Brevard	15	128	4723	71232

My intent was to create a report that lists schools with a similar number of schools (+/- 15%)  So if I select Alachua from a slicer, I would display the average number of students for all district that have between 62 and 83 schools.

I hope this helps; if the 'if' statements you mentioned might make this work.  Thanks!

[Greg_Deckler]

@Gutenvirt - OK, someone else may have a more brilliant solution to this but perhaps this is something that you can build off or get an idea from. I took your table and imported it twice, Schools and Schools2. To both I added a custom column:

Category = IF(Schools2[num_schs]<40,"Small",IF(Schools2[num_schs]<100,"Medium","Large"))

And created a Category list via Enter Data with Small Medium and Large as values in a Category column. Related both School tables to Category table, put district from School table in a slicer and added all of the data from School2 to a table. Clicking on the district slicer then filters the Schools2 table to only the districts in the same category.