Forum Discussion

Gutenvirt's avatar
Gutenvirt
Frequent Visitor
10 years ago

CALCULATE and filtering by aggregate comparison

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!

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    10 years ago

    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's avatar
      Gutenvirt
      Frequent Visitor
      10 years ago

      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:

       

      districtrndstrationum_schsclsrm_tch_ftenum_stds
      Alachua1672180428157
      Baker1693124985
      Bay1551179427053
      Bradford13122433239
      Brevard15128472371232

       

       

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        10 years ago

        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.