Apply filter based on value in other table and compute average

[sman]

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 to create a measure named "Region Average" that shows the average of averages of Amt over the stores that are in the region of the selected workshop. So for example, if a user selects store "1", the region is in "X" (with stores 1 and 2). Store 1 has an average Amt of 20, and store 2 has an average amt of 15. Thus I want to display the average of these, which is 17.5.

[miTutorials · Accepted solution]

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]

Hi Ismail,

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

[miTutorials]

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]

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]

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