topic Re: Need to Match Quantities (Supplied vs Ordered) in DAX Commands and Tips

Need to Match Quantities (Supplied vs Ordered)

pratichi — Fri, 14 Aug 2020 05:47:14 GMT

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.

Re: Need to Match Quantities (Supplied vs Ordered)

Greg_Deckler — Fri, 14 Aug 2020 06:14:46 GMT

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.

Re: Need to Match Quantities (Supplied vs Ordered)

amitchandak — Fri, 14 Aug 2020 06:15:53 GMT

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

Re: Need to Match Quantities (Supplied vs Ordered)

pratichi — Fri, 14 Aug 2020 07:10:52 GMT

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

Re: Need to Match Quantities (Supplied vs Ordered)

Anonymous — Fri, 14 Aug 2020 11:32:01 GMT

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

Re: Need to Match Quantities (Supplied vs Ordered)

calerof — Mon, 24 Jan 2022 00:13:53 GMT

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

Re: Need to Match Quantities (Supplied vs Ordered)

calerof — Mon, 24 Jan 2022 00:19:50 GMT

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

Thanks,