Forum Discussion
Weighted AVG DAX calculation
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] ) )
Community ChampionAnonymous
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] )
)
Really appreciate your time. Still getting error. Can't find name [Market_Value]. The field is under Sheet1 table.
- tamerj1
Anonymous
Ok we can eliminate the error but now I'm not sure what are you trying to calculate. So we are creating a calculated column in sheet1 (dim table) using values from two columns from sheet2 (fact table). I guess you want to retrieve the sum of the division after filtering out the none blank market prices? Is that correct?
Exactly! None blank [Market_Price] and while [HAS_Analytics] = 1
- tamerj1
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
Anonymous
I have edited the last solution adding the new filter (HAS_Analytics = 1)
So, under the filter is 2 different tables and not just Sheet1. [Market_Price] should be filtered from Sheet2.
- tamerj1
Anonymous
Sorry I got confused. I edited the code again please check. Again we are creating the column in sheet1 and all related columns are in sheet2
Still under the filter --
FILTER ( T1, Sheet1[Market_Price] <> BLANK () && Sheet2[HAS_Analytics] = 1 )
Suppose to be Sheet2[Market_Price]<>BlANK() && Sheet1[HAS_Analytics]=1)
But Im getting error here: Sheet1[HAS_Analytics]
- tamerj1
HAS_Analytics In which table?
Sheet1 table.