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 the error : "A single value of column 'Market_Price' in table 'Sheet1' cannot be determined. This can happenwhen measure formula refers to a column that contains many values without specifyingan aggregation such as min, max, count, or sum to get a single result".
Below is my formula but not sure what I am missing.
Weighted Avg =
DIVIDE ( SUMX ( FILTER ( Sheet2, Sheet2[HAS_Analytics] = 1 && Sheet1[Market_Price] <> BLANK () ),Sheet1[Market_Value] * Sheet1[Market_Price] ),
SUMX ( FILTER ( Sheet2, Sheet2[HAS_Analytics] = 1 && Sheet1[Market_Price] <> BLANK () ), Sheet1[Market_Value] ) )
Thank you.
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] ) )
19 Replies
Hi Anonymous
Please try
Weighted Avg = VAR T1 = ADDCOLUMNS ( Sheet2, "@MarketPrice", RELATED ( Sheet1[Market_Price] ) ) VAR T2 = FILTER ( T1, [HAS_Analytics] = 1 && [@MarketPrice] <> BLANK () ) RETURN DIVIDE ( SUMX ( T2, [Market_Value] * [@MarketPrice] ), SUMX ( T2, [Market_Value] ) )Anonymous
what is the relationship between the two tables?