SumProduct in DAX - Weighted Average

Anonymous On the first part, the weighted average is clearly correct at 13.0 as per my written example. However I need the weightings to rebalance to 100% dynamically, based on both whether there's any data in the metric column, or if there's any filters applied.

So for the Category 2 calc the 30%, 20% and 10% weights in products A, B and C become 50%, 33.3% and 16.7% respectively (because Product D is excluded due to lack of data).

So the weighted average calc (without filters applied) should be (0.5*12)+(0.33*15)+(0.167*9)=12.453

However, I'm hoping that it can also handle the user filtering the dashboard. Let's say they select only two products A and B (maybe the belong to another grouping that has been clicked on a pie chart in the dashbaord, or these two products have been directly selected).

But if these two only are selected, it should then rebalance again to 100% before calculating weighted average. So the 30% and 20% weights in product A and B become 60% and 40% respectively.

Therefore in this case the weighted averages would be:

Category1 = (0.6*10)+(0.4*20)=14

Category2 = (0.6*12)+(0.4*15)=13.2

Do you see what I'm getting at? Pardon the slightly different labels being used for the columns, but as it happens I've made good progress in the below linked PBIX.. I just can't get to the very last step.

https://drive.google.com/file/d/1XRZTiwFT0dKbZCifO2Q6fzzpHZuhwurd/view?usp=share_link

- "FlexibleGroupWeight" is working as it should, showing the total % the selected rows represent of total weights

- "Rebalanced" then successfully rebalances the original weight ("NameWeight") based on "FlexibleGroupWeight", such that the sum of this column = 100% as it should

- Then "ValueWeighted" is successfully rebalancing the original statistic ("NameValue") by the "Rebalanced" weight..

- so all I need is the final column to display the total of the "ValueWeighted" column (identical in each row)...

.. but because it's a measure rather than just a column it seems to be causing me issues. In the screenshot below, for example, it should simply sum 10.4469 + 1.3619 + -0.0583 + 0.7638 = 12.5131... But I'm getting 0.4 in each column at moment!?

I would REALLY appreciate your help here to get the final step sorted! I feel I am so close to having the perfect dynamic weighted average calculator...