Forum Discussion
Excel SUMIFS conversion help
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.
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))
3 Replies
kekepania0529 not familiar with SUMIF. Can you provide sample data and expected output.
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.
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))
