Forum Discussion

kekepania0529's avatar
kekepania0529
Icon for Helper I rankHelper I
4 years ago
Solved

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 Detai...
Need Help
  • kekepania0529's avatar
    kekepania0529
    4 years ago

    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's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

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.