Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Average by Product and Week Type

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.

 

ProductWeeksWEEK TYPEPriceSalesAverage per Product on SHELF WEEK
AB21-Oct-24SHELF$10.010,00010,000
AB28-Oct-24SHELF$10.010,00010,000
AB18-Nov-24PROMO$5.020,00010,000
BB21-Oct-24SHELF$8.040,00040,000
BB28-Oct-24SHELF$8.040,00040,000
BB18-Nov-24PROMO$4.080,00040,000
DAX AVERAGE

4 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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]

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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