Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
Proud to be a Super User!
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.
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.
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |