cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

SumProduct between mix of selected rows from Imported and Direct Query 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)

CategoryIdValueVolume
A10.111
A20.75
B30.87
B40.79
B50.26

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)

IdValue
10.9
40.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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Solution Sage
Solution Sage

@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

 

DataInsights_0-1603929748636.png

 

View solution in original post

Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
Solution Sage
Solution Sage

@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

 

DataInsights_0-1603929748636.png

 

View solution in original post

Community Support
Community Support

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.

View solution in original post

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors