Need to Match Quantities (Supplied vs Ordered)

[pratichi]

I have a Table 

Shipment No	Shipment Line no	Order  Number	Order Line no	Item Name	Supplied Quantity	Ordered Quantity
S1	1	O1	1	Item A	5	10
S1	2	O1	2	Item B	10	10
S2	1	O1	1	Item A	5	10
S3	1	O2	1	Item C	10	10

I want to check for every line in an order, the total shipped quantity is equal to the Order Quantity.

So for O1, Line 1, 10 units are order and if all 10 units have been supplied irrelevant of the no. of shipments.

As you can see item A in Order O1 has been shipped in 2 different shipments.

Since the order quantity is always going to show 10 for a particular order, irrespective to the supplied quantity, I am unable to figure out how can I match the supplied vs ordered quantity at the Order level.

I was thinking Concatenate Order-Item Name-Order line No. Then check Sum of Supplied quantity vs First/last of Ordered quantity but not sure how the formula would be.

I'm still a rookie in power BI and from a non-IT background, so any assistance on this would be very helpful.

Thanks in advance.

I see @Greg_Deckler @amitchandak  are amongst the best and kind on this forum, can you please guide me in the right direction.

[Anonymous]

// Returns True if item
// has been fully shipped.
// False if not. I assume
// that it's not possible
// to ship more than Ordered
// Quantity
[Shipped All] =  // calculated column, not a measure
var __orderNo = T[Order Number]
var __itemName = T[Item Name]
VAR __totalSupplied =
    SUMX(
        FILTER(
            T,
            T[Order Number] = __orderNo
            &&
            T[Item Name] = __itemName
        ),
        T[Supplied Quantity]
    )
RETURN
  ( T[Ordered Quantity] = __totalSupplied )

[amitchandak]

@pratichi , see it this measure work for remaining qty

sumx(Summarize(Table, table[Order Number],Table[Order Line no], Table[Item Name], "_1", sum(Table[Supplied Quantity]), "_2",max(Table[Ordered Quantity])),[_2]-[_1])

[Greg_Deckler]

@pratichi - You could create a calculated column like the following:

In Full = 
  VAR __SuppliedQuantity = SUMX(FILTER('Table',[Order Number] = EARLIER('Table'[Order Number]) && [Order Line] = EARLIER('Table'[Order Line]),[Supplied Quantity])
RETURN
  IF([Ordered Quantity] = __SuppliedQuantity,1,0)

Ones have been supplied in full, zeros have not.

[calerof]

Hi @Greg_Deckler ,

When I apply this calculated column I get an error:

<pi>A circular dependency was detected: Table_Order[Pending Order].</pi>

What am I missing?

Thanks,

Fernando

[calerof]

I've got it. I was using an explicit measure for quantity. When changed for the Quantity column it works magic.

Thanks,

F

[pratichi]

It reads The syntax for 'Return' is incorrect.

DAX(VAR __SuppliedQuantity = SUMX(FILTER('Table',[Order Number] = EARLIER('Table'[Order Number]) && [Order Line] = EARLIER('Table'[Order Line]),[Supplied Quantity])
RETURN
IF([Ordered Quantity] = __SuppliedQuantity,1,0)

It start marking a mistake right after Sumx. I believe thats because Sumx(table,expression) ?