SUMX HELP

[Alejandro9805]

Good morning,

I hope you can help me,

I created a measure to calculate the opportunity $ to fulfill pending orders, the items can be repeated in different orders, this makes that when I multiply the actual inventory by order to order, it doubles, triples the opportunity value, I tried to apply a filter, but the SUMX function does not allow me.

Is there any other way to do it?

DAX CODE When I multiply the value:

Opportunity $ = SUMX(Backorder,Backorder[B/O value] * RELATED('Inventory with location'[Qty on hand]))

DAX CODE:

Opportunity $ = SUMX(Backorder,Backorder[B/O value] * RELATED('Inventory with location'[Quantity available]),
FILTER(Backorder, Backorder[B/O qty] >= 'Inventory with location'[Qty on hand] ))}

Thanks for your help!

[Greg_Deckler]

@Alejandro9805 Try this:

Opportunity $ = 
  SUMX(
    FILTER(Backorder, Backorder[B/O qty] >= RELATED('Inventory with location'[Qty on hand]) )
    Backorder[B/O value] * RELATED('Inventory with location'[Quantity available])
  )

Otherwise, please post sample data and expected output

[Alejandro9805]

@Greg_Deckler 

Thanks for you help,

I did what you said,

DAX CODE:

Opportunity $ = SUMX(
FILTER(Backorder,Backorder[B/O qty] >= RELATED('Inventory with location'[Qty on hand]) )
Backorder,Backorder[B/O value] * RELATED('Inventory with location'[Qty available])
)

Error:

"Backorder" syntax is not correct.(DAX(SUMX( FILTER(Backorder,Backorder[B/O cant.] >= RELATED('Inventory with location'[Quantity available]) ) Backorder,Backorder[B/O value] * RELATED('Inventory with location'[Quantity available])))).

Thanks for you help.

[Greg_Deckler · Accepted solution]

@Alejandro9805 I missed a comma:

Opportunity $ = 
  SUMX(
    FILTER(Backorder, Backorder[B/O qty] >= RELATED('Inventory with location'[Qty on hand]) ),
    Backorder[B/O value] * RELATED('Inventory with location'[Quantity available])
  )