cancel
Showing results for
Search instead for
Did you mean:
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)

 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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Solution Sage

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``````

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.

2 REPLIES 2
Solution Sage

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``````

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.

## Helpful resources

Announcements

#### Check it Out!

Click here to read more about the November 2020 Updates!

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### 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