Dynamic Measure to calculate sales weighted price increase

[Chris_de]

Hello everyone,

I'm working on the following problem already far too long and looking for help now. I have a sales report which is basically structured like this:

I have 70.000 materials with sales data from the years 2017 to 2022. These materials are are assigned to a group structure. Every material has a division, a product main group and a product family. I want to calculate the sales weighted price increase and created my example in Excel first. So I added two columns. Blue is calculating the price. Orange is calculating the price increase based on the year before:

In Power BI I created two measures to do the job:

 

Price = DIVIDE(SUM('COPA Sales Portfolio'[turnover]),SUM('COPA Sales Portfolio'[quantity]))

Price Increase =

VAR ThisYear = SELECTEDVALUE('COPA Sales Portfolio'[year])

VAR PriceNow = [price]

VAR LastYear= ThisYear - 1

VAR PriceLastYear= CALCULATE([price], 'COPA Sales Portfolio'[year] = LastYear)

RETURN

    DIVIDE((PriceNow - PriceLastYear), PriceLastYear)

My Problem is the third measure for the sales weighted price increase. In Excel I created this:

It's basically the Price increase multiplied with the turnover of last year for each material I have selected via filters divided with the total turnover for all selected materials. 

So if I select a group or a division or a bunch of specific materials with filters the calculation has to be dynamic. I created various measures but nothing worked correctly and dynamic. Maybe anyone has an idea?

[v-jianboli-msft]

Hi @Chris_de ,

The formula you provided in the image is generated based on the row number, but there is no row number in the image.

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.