SumProduct between Imported and DirectQuery Tables

Hi All,

I have 2 tables A and B. Storage mode for A is 'Import' while for B is 'DirectQuery'.

I need to calculate X = (Sum Product of Value & Volume) / (Sum of Volume) for each category.

The following is the structure for the 2 tables:

Table A: (Storage Mode: Import)

For this table A: 
X = 0.287 for Category A ((0.1*11)+(0.7*5))/(11+5)
X = 0.595 for Category B ((0.8*7)+(0.7*9)+(0.2*6))/(7+9+6)

Table B: (Storage Mode: Direct Query)

However, the result I want is to use all values from Table A if the value for that Id is not present in Table B. Otherwise use Table B values. So in this case, 'Value' for Id 1 and 4 in table A should be taken from Table B.
In this manner the result would be:
X = 0.837 for Category A ((0.10.9*11)+(0.7*5))/(11+5)
X = 0.472 for Category B ((0.8*7)+(0.70.4*9)+(0.2*6))/(7+9+6)

I tried the below DAX but it is not working correctly:

VAR EditValue = 
	LOOKUPVALUE(
		TableB[Value],
		TableB[Id],
		SELECTEDVALUE(TableA[Id])
	)
	
RETURN
	SUMX(
		TableA,
		TableA[Volume] *
		IF(
			ISBLANK(EditValue) = FALSE(),
			EditValue,
			TableA[Value]
		)
	) / SUM(TableA[Volume])

This displays correct data at Id level but not at Catgeory Aggregation level. (I am sure this is because SELECTEDVALUE(TableA[Id]) returns blank for variable EditValue, when this measure is used at Catgeory level)

Let me know how this can be resolved. Appreciate any kind of help possible.

Thanks for you time!