- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calculate Average for a group with filters
Hello Experts,
I've struggled with these DAX for a while, it should be easy but couldn't get the results I want. Hope to get your guidance on this.
I want to be able to compare the average sales for a product and the average sales for the product category which the product belongs to and for the specific store that's been selected, so I guess I want to calculate:
1. the average sales for each product (by product_id), and
2. the average sales for each product category (product_category) by different stores (store_id). The store_id could be added as a slicer
What I tried:
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @XR043,
First, I would try to see if your issue is solved by adding the Product_Category on your table on the left. There is a specific behavior when using ALLEXCEPT where if a column you are using in ALLEXCEPT is not preset in the filter context, it won't work.
In your use case, Product_Category is not explicitly present in the table so you won't be able to use ALLEXCEPT for that column. Hope it helps.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @XR043,
First, I would try to see if your issue is solved by adding the Product_Category on your table on the left. There is a specific behavior when using ALLEXCEPT where if a column you are using in ALLEXCEPT is not preset in the filter context, it won't work.
In your use case, Product_Category is not explicitly present in the table so you won't be able to use ALLEXCEPT for that column. Hope it helps.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yep, here's the model:

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
08-27-2024 06:13 AM | |||
01-31-2024 07:03 AM | |||
07-03-2024 07:42 AM | |||
08-29-2024 12:51 PM | |||
07-23-2024 06:34 AM |
User | Count |
---|---|
14 | |
14 | |
11 | |
11 | |
8 |