Calculate Converted Value Measure using SUMX from different tables

[mangesh]

Hello, I am a Power BI beginner and this forum has been extremely helpful in guiding me in the right direction when I get stuck. I have just started learning DAX and have managed to create simple formulas. However I am now stuck and not able to figue out how to proceed. Any guidance will be appreciated.

Problem:

Below is the relationship diagram of my data model.  Historical data is static and not refreshed. Monthly sales is added to new table and refreshed daily. I have a conversion factor for every month for every item code in my yield master. To arrive at sales value, I need to multiply the quantity for each item (available in invoice data table on daily basis) by its conversion factor for that particular month and then aggregate at various levels. I understand that SUMX is the way to go but since the data is in different tables and i need to iterate multiplication at variouls levels I have no clue how to proceed. 

The formula logic is:

Value = Sum of (quantity against every item code for a month*conversion factor from item master for that month)

[v-jiascu-msft]

Hi @mangesh,

Try the following formula please. If it doesn't help, please provide a dummy sample.

Measure =
SUMX (
    'Feb 2018 Invoice Data',
    [Quantity] * RELATED ( 'Tbl_ItemMaster'[Converted Reporting Factor] )
)

Best Regards,

Dale

[mangesh]

Thanks @v-jiascu-msft for replying. The conversion factor is in Yield Master which is connected to Feb Invoice Table via Item Master Table. There is no direct connection between Yield Master and Invoice data. So the formula didnt work. Attaching the data model again.

Formula Logic: Yield Total Value = Sum of ( Item Quantity (from Invoice table)*Yield Non-Conv Vol (from Yield Master Table) )