Forum Discussion

sman's avatar
sman
Frequent Visitor
2 years ago
Solved

Apply filter based on value in other table and compute average

Hi! I have two tables. Sales and Stores:       I've connected the tables through the "Store" columns, and I've added a slicer that let's the user filter on the store. Now, I want t...
Need Help
  • miTutorials's avatar
    miTutorials
    2 years ago

    Try the below measure.

     

     

    Avg_New = CALCULATE(AVERAGE('Table'[Avg]),ALL('Table'[Store]))

     

     

     

    Best regards,

    Ismail | MiTutorials

    🎥 MiTutorials on YouTube

    Explore over 200+ Power BI tutorials and enhance your skills!

miTutorials's avatar
miTutorials
Icon for Super User rankSuper User
2 years ago

Try the below measure.

 

 

Avg_New = CALCULATE(AVERAGE('Table'[Avg]),ALL('Table'[Store]))

 

 

 

Best regards,

Ismail | MiTutorials

🎥 MiTutorials on YouTube

Explore over 200+ Power BI tutorials and enhance your skills!

sman's avatar
sman
Frequent Visitor
2 years ago

Hi Ismail,

 

Won't this just the take average amount over all regions?

  • miTutorials's avatar
    miTutorials
    Icon for Super User rankSuper User
    2 years ago

    If you have the region field on the table, it will not. Do give it a try and let me know if this does not work! We can then tweak the code.

    • sman's avatar
      sman
      Frequent Visitor
      2 years ago

      It doesn't work, instead of the expected value of 17.5, I get 15.

      I can obtain the result I want through this

       

      Average new = 
VAR selectedRegion = selectedvalue(Stores[Region])

RETURN CALCULATE(
    Average(Sales[Amt]),
    ALL(Stores),
    Stores[Region] == selectedRegion
)

       

       

      But doing this I clear all my other filters. For example, let's say I have another column on sales which is "ARTICLE_CATEGORY" (e.g. shoes, pants etc). If a user has selected pants, I don't want to drop this filter. According to the documentation (ALL function (DAX) - DAX | Microsoft Learn) I'm supposed to be able to pass the columns which I want to drop but I'm not sure how to apply it here...

      Edit: It wasn't that hard! Got it to work by specifying another calculate call with a filter on selected product categories! Thank you!

      • miTutorials's avatar
        miTutorials
        Icon for Super User rankSuper User
        2 years ago

        Within ALL you should just pass in the column Name (Store in your example) and not the entire table.