Forum Discussion

Alejandro9805's avatar
Alejandro9805
New Member
3 years ago
Solved

SUMX HELP

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!

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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])
  )

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      Alejandro9805
      New Member
      3 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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])
  )