Calculate Average for a group with filters

[XR043]

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:

AVG Customer Sales =

AVERAGEX('Sales by Store',

'Sales by Store'[quantity_sold] * 'Sales by Store'[unit_price])

AVG Customer Sales_Category by StoreID =

CALCULATE(

    [AVG Customer Sales],

    ALLEXCEPT('Sales by Store','Product Lookup'[product_category],'Sales by Store'[store_id])

)

However the snapshot below shows that I didnt actually achieve this. I think the AVG Customer Sales_Category by StoreID should show 21.10 which is the average for Coffee beans in Store 3, which the product (product_id =1) belongs to.

I was wondering if anyone can give me some ideas on what to do. Thank you.

[m3tr01d · Accepted solution]

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.

[daXtreme]

@XR043 

Can you please post a picture of your model?

Thanks.

[XR043]

Yep, here's the model: