topic Re: Calculate Average for a group with filters in DAX Commands and Tips

Calculate Average for a group with filters

XR043 — Sun, 04 Sep 2022 08:13:23 GMT

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.

Re: Calculate Average for a group with filters

daXtreme — Sun, 04 Sep 2022 08:37:56 GMT

XR043

Can you please post a picture of your model?

Thanks.

Re: Calculate Average for a group with filters

XR043 — Sun, 04 Sep 2022 08:43:38 GMT

Yep, here's the model:

Re: Calculate Average for a group with filters

m3tr01d — Mon, 05 Sep 2022 12:50:50 GMT

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.