topic Re: Weighted AVG DAX calculation in DAX Commands and Tips

Weighted AVG DAX calculation

Anonymous — Fri, 16 Sep 2022 18:54:07 GMT

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.

Re: Weighted AVG DAX calculation

tamerj1 — Fri, 16 Sep 2022 19:09:10 GMT

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] ) )

Re: Weighted AVG DAX calculation

Anonymous — Fri, 16 Sep 2022 19:20:28 GMT

Hi Tamerj - Thank you for the response. I'm still getting the same error on [Market_Price]

Re: Weighted AVG DAX calculation

tamerj1 — Sat, 17 Sep 2022 02:37:28 GMT

Anonymous

what is the relationship between the two tables?

Re: Weighted AVG DAX calculation

Anonymous — Thu, 22 Sep 2022 18:23:08 GMT

Hi Tamerj-

Re: Weighted AVG DAX calculation

tamerj1 — Thu, 22 Sep 2022 18:28:02 GMT

Anonymous

In your example, which one is table sheet1? Also please confirm that the relationship is one to many single way

Re: Weighted AVG DAX calculation

Anonymous — Thu, 22 Sep 2022 18:35:15 GMT

Sorry, I mistakenly clicked "Accepted Solution"

Yes, the relationship is many to single way.

Sheet1 = POSITION_FACT_WH

Sheet2 = SECURITY_DIM_WH

Re: Weighted AVG DAX calculation

tamerj1 — Thu, 22 Sep 2022 18:39:27 GMT

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?

Re: Weighted AVG DAX calculation

Anonymous — Thu, 22 Sep 2022 18:44:04 GMT

The Sum.

Re: Weighted AVG DAX calculation

tamerj1 — Thu, 22 Sep 2022 18:48:22 GMT

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] ) )

Re: Weighted AVG DAX calculation

Anonymous — Thu, 22 Sep 2022 19:04:45 GMT

tamerj1

Really appreciate your time. Still getting error. Can't find name [Market_Value]. The field is under Sheet1 table.

RETURN

DIVIDE (

SUMX ( T2, [Market_Value]* [@MarketPrice] ),

SUMX ( T2, [Market_Value] )

)

Re: Weighted AVG DAX calculation

tamerj1 — Thu, 22 Sep 2022 19:14:27 GMT

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?

Re: Weighted AVG DAX calculation

Anonymous — Thu, 22 Sep 2022 19:21:00 GMT

Exactly! None blank [Market_Price] and while [HAS_Analytics] = 1

Re: Weighted AVG DAX calculation

tamerj1 — Thu, 22 Sep 2022 20:10:15 GMT

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] ) )

Re: Weighted AVG DAX calculation

tamerj1 — Thu, 22 Sep 2022 19:24:01 GMT

Anonymous

I have edited the last solution adding the new filter (HAS_Analytics = 1)

Re: Weighted AVG DAX calculation

Anonymous — Thu, 22 Sep 2022 19:35:32 GMT

So, under the filter is 2 different tables and not just Sheet1. [Market_Price] should be filtered from Sheet2.

Re: Weighted AVG DAX calculation

tamerj1 — Thu, 22 Sep 2022 19:59:57 GMT

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

Re: Weighted AVG DAX calculation

Anonymous — Thu, 22 Sep 2022 20:08:37 GMT

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]

Re: Weighted AVG DAX calculation

tamerj1 — Thu, 22 Sep 2022 20:11:09 GMT

HAS_Analytics In which table?

Re: Weighted AVG DAX calculation

Anonymous — Thu, 22 Sep 2022 20:12:04 GMT

Sheet1 table.