topic Average by Product and Week Type in DAX Commands and Tips

Average by Product and Week Type

Anonymous — Fri, 17 May 2024 20:30:57 GMT

Hello Team,

I apologize if this question has already been addressed, but I haven't been able to find a solution. I've been struggling with this issue for the past couple of days and it's causing me quite a headache. Could you please assist me in achieving the result in the last column using either a measure or a column? Thank you in advance for your assistance.

Product	Weeks	WEEK TYPE	Price	Sales	Average per Product on SHELF WEEK
AB	21-Oct-24	SHELF	$10.0	10,000	10,000
AB	28-Oct-24	SHELF	$10.0	10,000	10,000
AB	18-Nov-24	PROMO	$5.0	20,000	10,000
BB	21-Oct-24	SHELF	$8.0	40,000	40,000
BB	28-Oct-24	SHELF	$8.0	40,000	40,000
BB	18-Nov-24	PROMO	$4.0	80,000	40,000

Re: Average by Product and Week Type

DataNinja777 — Sat, 18 May 2024 05:29:33 GMT

Hi Anonymous ,

Are you trying to calculated the weigted average of the products over different weeks? In that case, it can be calculated in the following mannter as an example.

If you you required output is different one, please let me know.

I attach an example pbix file.

Best regards,

Re: Average by Product and Week Type

Anonymous — Sat, 18 May 2024 06:56:38 GMT

Hi,

Thanks for looking on this. Unfortunately this is not what I'm looking for. I need result exactly as in column [Average per Product on SHELF WEEK]

Re: Average by Product and Week Type

DataNinja777 — Sat, 18 May 2024 09:44:31 GMT

Hi Anonymous ,

Thank you for your feedback. I've redone the calculated column to produce your required output.

I also attach the pbix file as an example.

Best regards,

Re: Average by Product and Week Type

Anonymous — Mon, 20 May 2024 05:26:33 GMT

Hi, Anonymous

You can refer to DataNinja777 reply. If it does not work, you can try the following measure.

MEASURE:

Average per Product on SHELF WEEK 1 = VAR _product = SELECTEDVALUE ( 'Table'[Product] ) VAR _result = CALCULATE ( SUMX ( FILTER ( ALL ( 'Table' ), 'Table'[Product] = _product && 'Table'[WEEK TYPE] = "SHELF" ), 'Table'[Price] * 'Table'[Sales] ) / SUMX ( FILTER ( ALL ( 'Table' ), 'Table'[Product] = _product && 'Table'[WEEK TYPE] = "SHELF" ), 'Table'[Price] ) ) RETURN _result

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum