SumProduct between mix of selected rows from Imported and Direct Query tables

[akul]

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)

Category	Id	Value	Volume
A	1	0.1	11
A	2	0.7	5
B	3	0.8	7
B	4	0.7	9
B	5	0.2	6

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)

Id	Value
1	0.9
4	0.4

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)

Thanks for you time!

[DataInsights · Accepted solution]

@akul,

Try this solution.

1. Create calculated column in TableA:

Value to Use =
VAR vTableBValue =
    LOOKUPVALUE ( TableB[Value], TableB[Id], TableA[Id] )
VAR vResult =
    IF ( ISBLANK ( vTableBValue ), TableA[Value], vTableBValue )
RETURN
    vResult

2. Create measure:

Sum Product = 
VAR vNumerator =
    SUMX ( TableA, TableA[Value to Use] * TableA[Volume] )
VAR vDenominator =
    SUM ( TableA[Volume] )
VAR vResult = 
    DIVIDE ( vNumerator, vDenominator )
RETURN
    vResult

[Icey · Accepted solution]

Hi @akul ,

@DataInsights's DAX expression works well. If you have no other questions, please accept his reply as a solution so that people who may have the same question can get the solution directly.

In addition, you can just create one measure like so:

Measure = 
VAR t =
    ADDCOLUMNS (
        TableA,
        "Value_to_use",
            VAR vTableBValue =
                LOOKUPVALUE ( TableB[Value], TableB[Id], TableA[Id] )
            VAR vResult =
                IF ( ISBLANK ( vTableBValue ), TableA[Value], vTableBValue )
            RETURN
                vResult
    )
RETURN
    VAR vNumerator =
        SUMX ( T, [Value_to_use] * [Volume] )
    VAR vDenominator =
        SUMX ( t, [Volume] )
    VAR vResult =
        DIVIDE ( vNumerator, vDenominator )
    RETURN
        vResult

Best Regards,

Icey

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

[DataInsights · Accepted solution]

@akul,

Try this solution.

1. Create calculated column in TableA:

Value to Use =
VAR vTableBValue =
    LOOKUPVALUE ( TableB[Value], TableB[Id], TableA[Id] )
VAR vResult =
    IF ( ISBLANK ( vTableBValue ), TableA[Value], vTableBValue )
RETURN
    vResult

2. Create measure:

Sum Product = 
VAR vNumerator =
    SUMX ( TableA, TableA[Value to Use] * TableA[Volume] )
VAR vDenominator =
    SUM ( TableA[Volume] )
VAR vResult = 
    DIVIDE ( vNumerator, vDenominator )
RETURN
    vResult