Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |