The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to Solution.
Try the below measure.
Avg_New = CALCULATE(AVERAGE('Table'[Avg]),ALL('Table'[Store]))
Best regards,
Ismail | MiTutorials
Explore over 200+ Power BI tutorials and enhance your skills!
Try the below measure.
Avg_New = CALCULATE(AVERAGE('Table'[Avg]),ALL('Table'[Store]))
Best regards,
Ismail | MiTutorials
Explore over 200+ Power BI tutorials and enhance your skills!
Hi Ismail,
Won't this just the take average amount over all regions?
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.
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!
Within ALL you should just pass in the column Name (Store in your example) and not the entire table.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
79 | |
79 | |
39 | |
36 |
User | Count |
---|---|
161 | |
111 | |
64 | |
59 | |
54 |