column value in master table to be updated based on conditional sumx value in related table

[GVTionale]

I HAVE 2 TABLES
ORDER MASTER

ORDER_NUMBER	CUSTOMER
101/2020	GABRIEL
120/2020	ADRIEN
124/2020	DIANA

ORDER DETAILS

ORDER_NUMBER	CUSTOMER	ITEM_ID	ORDER QTY	SHIPPED QTY	BALANCE QTY
101/2020	GABRIEL	12345	100	100
120/2020	ADRIEN	45678	100	40	60
120/2020	ADRIEN	87654	60	60	0
124/2020	DIANA	97654	200		200

PROBLEM :
I NEED TO DERIVE "SHIPMENT STATUS" FOR THE ORDER NUMBER AND UPDATE IN SHIPMENT STATUS COLUMN IN MASTER TABLE BASED ON FOLL. CONDITIONS - 

IF ALL ITEMS IN THE ORDER ARE FULLY SHIPPED, SHIPMENT STATUS ="SHIPPED"
IF SOME ITEMS ARE SHIPPED AND THERE IS BALANCE OF OTHERS, SHIPMENT STATUS="PARTIALLY SHIPPED"
IF NO SHIPMENT HAS BEEN MADE AND FULL ORDER QTY IS PENDING, SHIPMENT STATUS="PENDING"

[amitchandak]

@GVTionale 

You can create a new column in order master like this

Balance = sumx(filter(ORDERDETAILS,ORDERDETAILS[ORDER_NUMBER]=ORDERMASTER[ORDER_NUMBER]),ORDERDETAILS[ORDER QTY]-ORDERDETAILS[SHIPPED QTY])

Now you can use Switch true or If to create the status column

[GVTionale]

Hi

i have already created a column in my details to calculate the balance qty.

what i need now is the summed up result to reflect the position at document level to update the status in the master.

if can get the status of partial shipment vs fully shipped as well it will be great (if shipped qty>0 and pending qty>0 it will be partially shipped)

regards

[GVTionale]

Dear Amit

Thanks for your response.

is it possible to have only the Shipment Status column in the Master and update using a single If and filter dax mentioned by you, instead of one more column with balance qty?

regards

[v-chuncz-msft]

@GVTionale 

You may refer to the DAX below.

Column =
SWITCH (
    TRUE (),
    ISEMPTY (
        FILTER (
            RELATEDTABLE ( DETAILS ),
            DETAILS[BALANCE QTY] > 0
        )
    ), "SHIPPED",
    ISEMPTY (
        FILTER (
            RELATEDTABLE ( DETAILS ),
            DETAILS[SHIPPED QTY] > 0
        )
    ), "PENDING",
    "PARTIALLY SHIPPED"
)

[GVTionale]

Thanks for you response. couple of queries -

(1)in your DAX where are you summing up the quantities of items in the details to determine if the document is shipped or pending etc.?

(2) the formula i would like to apply is -

if sum of balance qty for the doc<=0 status= shipped

else

if sum of balance qty for the doc>0 and shipped qty>0, partially shipped

else

status=pending

I tried applying sumx to the dax u sent me but the result is wrong

regards

Sorry I am new and self learner of Power BI and hence will need further help as i got erroneous results

regards