Forum Discussion
Weighted AVG DAX calculation
Hi All - This is basically where i pick the fields needed from two tables. My error is coming from the "Sheet1" table where it flags the field with red line. On looking up the meaning of th...
Anonymous
In your example, which one is table sheet1? Also please confirm that the relationship is one to many single way
Anonymous
Please try
Weighted Avg = VAR T1 = RELATEDTABLE ( Sheet2 ) VAR T2 = FILTER ( T1, Sheet2[Market_Price] <> BLANK () && Sheet2[HAS_Analytics] = 1 ) RETURN SUMX ( T2, DIVIDE ( Sheet2[Market_Value] * Sheet2[Market_Price], Sheet2[Market_Value] ) )
tamerj1
Community Champion
Community ChampionAnonymous
In your example, which one is table sheet1? Also please confirm that the relationship is one to many single way
Sorry, I mistakenly clicked "Accepted Solution"
Yes, the relationship is many to single way.
Sheet1 = POSITION_FACT_WH
Sheet2 = SECURITY_DIM_WH
- tamerj1
Anonymous
In this case you have many values of the market price related to each row of the security dim table. Which value would you like to retrieve? The max, min, latest or sum?
The Sum.
- tamerj1
Anonymous
Weighted Avg = VAR T1 = ADDCOLUMNS ( Sheet2, "@MarketPrice", SUMX ( RELATEDTABLE ( Sheet1 ), Sheet1[Market_Price] ) ) VAR T2 = FILTER ( T1, [HAS_Analytics] = 1 && [@MarketPrice] <> BLANK () ) RETURN DIVIDE ( SUMX ( T2, [Market_Value] * [@MarketPrice] ), SUMX ( T2, [Market_Value] ) )