Forum Discussion
CALCULATE and filtering by aggregate comparison
Community ChampionAt 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")
?
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.