Calculated column value based on delivered status of all parts in an order

[comacabana]

Hello,

I've been stuck with this issue for quite a while now and I still haven't found any concrete answers.

I have a list of order numbers, and each order could contain multiple products that are being sold. For my calculated column I'd like to have the value yes or no show up depending on the status of one order. If all items are are delivered I would like it to say yes, and other statuses containing anything but delivered as no. I feel like the solution is really simple but I can't get my head around what to do.

I've added an example of what I'd like to acheive below. The complete delivery column is what I want to be the calculated column in PBI

I appreciate any help. 

Order Number	Part	Status	Compelte delivery
C140815	Bolt	Invoiced/Closed	No
C140817	Screw	Invoiced/Closed	No
C140817	Nut	Invoiced/Closed	No
C140944	Bearing	Partially Delivered	No
C140944	Belt	Released	No
C140944	Nut	Picked	No
C140946	Bolt	Delivered	Yes
C140946	Light	Delivered	Yes
C141231	Panel	Invoiced/Closed	No
C142329	Pump	Picked	No
C142331	Bolt	Invoiced/Closed	No
C145635	Switchboard	Delivered	Yes
C145635	Nut	Delivered	Yes
C145635	Bearing	Delivered	Yes

[amitchandak · Accepted solution]

@comacabana , a new column

new column =
var _cnt = countx(filter(Table, [Order Number] =earlier([Order Number])),[Order Number])
var _cnt1 = countx(filter(Table, [Order Number] =earlier([Order Number]) && [Status] = ""Delivered),[Order Number])
return
if(_cnt = _cnt1 , "Yes", "No")

[comacabana]

This worked perfectly thank you so much!