AVERAGEX with RELATED function

[Spartanos]

Hi,
I want to calculate the average price per month, with a lookup (related function) in DAX, and with additional constraints.

I created both a calulated (weight_factor) column as well as a meassure for weight factor. I don't what is the best to use, either the caluclated column or the DAX measure for weight_factor.

Table1

Overll charge    Month       Weight_factor  TEX    Routing port

200                    2022-04    1                       2        TWKEL - USLAX

300                   2022-04     2                       1        USLGB - AUMEL

0                       2022-04     2                       2        USLGB - AUMEL

Table2

Routing port            Ticker

TWKEL - USLAX        FB

USLGB - AUMEL       FB

The idea is to calculate the average price per ticker, per month, wherby in table 1 the overall charge should be >0, the weight factor should be 1, and the TEX should be 2. The outcome would be that the average price for FB 2022-04 is 200.

Any help is more than welcome!

[tamerj1 · Accepted solution]

Hi @Spartanos 
Creae a relationship between the two tables then create MEASURE >

Average Price = 
CALCULATE ( 
    AVERAGE ( Table1[Overll Charge] ), 
    Table1[Overll Charge] > 0, 
    Table1[Weightt_factor] = 1,
    Table1[TEX] = 2
)

Here is a sample file  https://www.dropbox.com/t/nVDrfEioumlKqKZb