Order_Qty DAX calculation with Multiple deliveries

DAX DAX Calculations
1

Dear EDNA Experts,

Looking for DAX solution to calculate order qty without duplicating numbers,

Purchase_Order No Sales_Order No Product_Code Order_Qty Delivery_Qty Delivery Date Delivery
PO101 SO101 A 1000 1000 01-01-2021 Full Delivery
PO102 SO102 B 1500 500 01-02-2021 Partial Delivery
PO102 SO102 B 1500 750 10-02-2021 Partial Delivery
PO102 SO102 B 1500 250 15-02-2021 Partial Delivery
PO103 SO103 C 200 200 01-03-2021 Full Delivery
PO103 SO103 C 250 200 01-04-2021 Partial Delivery
PO103 SO103 C 250 50 01-05-2021 Partial Delivery
PO104 SO104 A 1000 750 01-06-2021 Partial Delivery
PO104 SO105 A 500 500 01-06-2021 Full Delivery

Solution table will look like below,

Purchase_Order No Sales_Order No Product_Code Order_Qty Delivery_Qty PO Completion
PO101 SO101 A 1000 1000 100%
PO102 SO102 B 1500 1500 100%
PO103 SO103 C 450 450 100%
PO104 SO104 A 1500 1250 83%

Regards
Jamie

2

Hello @James_Thomas,

Thank You for posting your query onto the Forum.

Well, first thing that I’ve observed that against “Purchase Order No: PO104” you’ve assigned “Order_Qty” as 1500. But the thing is when you’ll put “Sales_Order No” field into the table visual it’ll always show the bifurcation between those two orders in the form of 1000 and 500. Below is the screenshot provided for the reference -

Results - 1
Results - 1744×224 7.33 KB

If you want a consolidated Order_Qty against the Purchase_Order No then in that case you’ll have to exclude the field from the visual so it’ll not show the bifurcation of Qty with respect to Sales_Order No. Below is the screenshot provided for the reference -

Results - 2

Below are the formulas provided for the reference to achieve the results -

Order Qty = 
SUMX(
    SUMMARIZE(
        'Table' , 
        'Table'[Purchase_Order No] , 
        'Table'[Order_Qty] ) , 
    'Table'[Order_Qty] 
)


Delivery Qty = 
SUM( 'Table'[Delivery_Qty] )


Delivery Completion = 
DIVIDE( [Delivery Qty] , [Order Qty] , 0 )

I’m also attaching the working of the PBIX file for the reference as well.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Order Qty - Calculation.pbix (22.2 KB)

3

Hi Harsh,

Yes. That’s correct.

Regards
James