Excel SUMIFS conversion help

[kekepania0529]

I have the following excel formula that I need to convert to DAX for Power BI (Power BI column names are in italics within the excel formula):

Area Contract Price = IFERROR(SUMIFS('Contract Details'!$Z:$Z Sell Extended Price,'Contract Details'!$D:$D Area,'Inbound Report'!$H14 Area,'Contract Details'!$L:$L Product Class,'Inbound Report'!$B14 Material Grade)/SUMIFS('Contract Details'!$Y:$Y Sell Order LB,'Contract Details'!$D:$D Area,'Inbound Report'!$H14 Area,'Contract Details'!$L:$L Product Class,'Inbound Report'!$B14 Material Grade),"")

Contract Details and Inbound Reports are unrelated tables in my Power BI report. I cannot create a relationship because neither table has a unique identifier (many:many). The join is on Inbound Report.Area = Contract Details.Area and Inbound Report.Material Grade = Contract Details.Product Class.

[kekepania0529 · Accepted solution]

i was able to get my desired results with this :

Area Contract Pricing = AVERAGEX(FILTER( 'Contract Detail','Contract Detail'[Area] = 'Inbound Detail'[Area] &&

          'Contract Detail'[Product Class Desc] = 'Inbound Detail'[Material Grade]), 

          DIVIDE('Contract Detail'[Sell Extended Price], 'Contract Detail'[Sell Ordered LB], 0))

[AlexisOlson]

SUMIFS is a sum of rows that satisfy the listed matching conditions.

In DAX, you can use filters in a similar way. This might work as a calculated column on 'Inbound Report'.

Numerator =
CALCULATE (
    SUM ( 'Contract Details'[Sell Extended Price] ),
    'Contract Details'[Area] = 'Inbound Report'[Area],
    'Contract Details'[Product Class] = 'Inbound Report'[Material Grade]
)

You can replace 'Inbound Report'[Area] and 'Inbound Report'[Material Grade] with specific values for this to work as a measure.

[smpa01]

@kekepania0529 not familiar with SUMIF. Can you provide sample data and expected output.