DAX Measure weighted sum on multiple hierarchy levels

[Butterbirne]

Hi there,

I have the following problem, which I hope you can help me with.

Final achievment should be to have a weighted price development ratio over products, product groups and customers. I have a matrix visual including that hierarchy like in the simplified Excel sample below (in my real model btw I have 6 hierarchy levels). On single product level I have a price development ratio (column D) which should be weighted with sales percentage in column C. What grinds my gears is to add that up for the upper hierarchy levels. 

On product group level it is the sum of the weighted product ratios but on customer level it has to be the sum of the weighted product group ratios.

My current approach is to calculate the weighted ratio on each level and then using that measure for the next hierarchy level as a basis but for my current data model with around half a million rows this iteration is way to heavy. Formula for product group level ratio would look like:

Weighted Product Group Ratio = IF(ISINSCOPE('Table'[Product Group),SUMX(VALUES('Table'[Product),[Price Development Ratio]*[Sales %]))

Is there any measure to calculate this in a less time consuming way?

If you need additional info let me know. Hope my requirement is somehow understandbable 😄

Any help is much appreciated, cheers!

[lbendlin]

looks like you are on the right track. Please quantify "time consuming".