Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Adding your Averages together when selecting multiple filters

Hi, 

 

I currently averaging all of my data by month for a certain area, but when I try to filter to multiple areas, I want them to aggregate and not maintain the true average. 

 

Area         Month         Amount

TX               Jan-19         10

TX              Feb-19         20

OK              Jan-19         30

OK              Feb-19        40

 

I have the "area" as a filter, and I am averaging the amount

 

TX = 15

OK = 35

 

When, I select multiple filters to see how much I am spending across both, I would like them to aggregate together, so my answer is "TX average + OK average = 50".

I am currently getting the average of the 2 areas (15+35)/2 = 25.

 

Any help?

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous  ,

    Try this out:

    MEASURE 1 = 
SUMX( 
         SUMMARIZE(    Table3 , 
                       Table3[Area] , 
                       "AVERAGE BY AREA" , AVERAGE( Table3[Amount] ))
  ,   [AVERAGE BY AREA] )

    This is the outcome:

    Let me know if that worked for you.
    Cheers,
    Rob

     

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous  ,

    Try this out:

    MEASURE 1 = 
SUMX( 
         SUMMARIZE(    Table3 , 
                       Table3[Area] , 
                       "AVERAGE BY AREA" , AVERAGE( Table3[Amount] ))
  ,   [AVERAGE BY AREA] )

    This is the outcome:

    Let me know if that worked for you.
    Cheers,
    Rob

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This works perfectly! Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      How many expressions can the SUMX measure handle? 

       

      For example if I also wanted to add in "type". Where if I added another filter for Type, it would aggregate by area AND by type.