Average by Product and Week Type

[Anonymous]

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

[Anonymous]

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

[DataNinja777]

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,

[Anonymous]

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]

[DataNinja777 · Accepted solution]

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,