Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

How to find Weighted Values for week

Hi Guys,
How to implement this formula in Power BI
Current Price for Week_1/Comp_1 Price for Week_1 for all PMIDs weighted by Historical_Volume for Week_1
So i have written logic like this which is not giving excepted result as the ask is Week wise

(t_price_edit_cpi_history. Currentprice / t_price_edit_competitor_cpi_history. REGULAR_PRICE ) * t_price_edit_cpi_history.TOTAL_UNITS


I have week , Currenprice ,Comp_Price(Regular_price) and Volume (Total_Units) columns.. we need to calulcated week wise values...Could any one help on this please and I'm attaching excel here for sample data which implements in Excel

 

 



Need Help

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous  - create a below measure:

    Weighted_Price_Ratio =
    VAR TotalVolume =
    CALCULATE(
    SUM(t_price_edit_cpi_history[TOTAL_UNITS]),
    ALLEXCEPT(t_price_edit_cpi_history, t_price_edit_cpi_history[Week])
    )

    VAR WeightedSum =
    SUMX(
    t_price_edit_cpi_history,
    (t_price_edit_cpi_history[Currentprice] / t_price_edit_competitor_cpi_history[REGULAR_PRICE]) *
    t_price_edit_cpi_history[TOTAL_UNITS]
    )

    RETURN
    DIVIDE(WeightedSum, TotalVolume, 0)

     

     

    if possible, upload your sample Excel file if you need a more specific solution

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

     

    Thanks for the reply from rajendraongole1 .

     

    My following up is just to ask if the problem has been solved?

     

    If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

     

    Thank you very much for your cooperation!

     

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

     

    My following up is just to ask if the problem has been solved?

     

    If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

     

    Thank you very much for your cooperation!

     

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi @Bajigntrr ,

     

    My following up is just to ask if the problem has been solved?

     

    If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

     

    Thank you very much for your cooperation!

     

    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!