Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RilwanFlame
Helper III
Helper III

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. 

2 ACCEPTED SOLUTIONS

@RilwanFlame 

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

View solution in original post

@RilwanFlame 

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

 

 

 

View solution in original post

19 REPLIES 19
tamerj1
Super User
Super User

Hi @RilwanFlame 

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

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

@RilwanFlame 

what is the relationship between the two tables?

Hi Tamerj- 

 

RilwanFlame_0-1663870954249.png

 

@RilwanFlame 

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

 

@RilwanFlame 

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. 

 

@RilwanFlame 

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

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

@RilwanFlame 

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?

@RilwanFlame 

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

 

 

 

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] 

HAS_Analytics In which table?

Sheet1 table. 

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

@RilwanFlame 

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

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

@RilwanFlame 

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.