Help please, guidance required

[Snowy34]

Good day all,

Below I have a simple table of data something like the below example, the CO number ala the customer order is the number our system gives to each customer order, now the delivery number is the number used to deliver the stock to the customer. Its always liked the CO number but it's not always a 1 to 1 ratio as per the below example. One CO number but two delivery numbers as the stock was delivered in two stages. 

What I'm trying to measure is the following,

1. How many perfect customer orders have we delivered, an example is a 2end order 1005552 its perfect order, 1 co 1 delivery number and all delivered in full. 

2. I would like to flag every bad order aka order 1005551 and then work out how many days it takes from start to finish, in this case, it's taken 5 days to deliver the complete order. 

Customer Order	Delivery Number	Customer Requested Date	Departure Date	Item	Order qty	delivered qty
1005551	5551	15/11/19	16/11/19	a1	2	2
1005551	5552	15/11/19	20/11/19	b2	1	1
1005552	6661	20/11/19	20/11/19	a1	2	2

Thank you all and I look forward to your answers to my questions 🙂

Regards 

Snowy

[venal · Accepted solution]

@Snowy34 

Please follow the below steps.

1. Right click on table (Ex:- DOP-055 1)

2. Click on "Duplicate", you will get another table. Remove unnecessary columns and keep required columns. (Ex:- ONo,DelNo,CusReqDt,DepDt,Item,ItemOrderQty,ItemDelQty).

3. Clcik on "Group By" based on columns. (Ex:- Ono - only one i used here, based on your requirement you can use the columns here).

4. Select the "Original Table (Ex:- DOP-055 1)" and click on "Merge Queries" and select the common columns and clikc ok.

5. Expand the column and select required columns and change the column by double click on it (Ex:- Cnt).

6. Click "Close and Apply".

7. Now you can able see the "Cnt" column in Main Table and Click on "New Column" and assign it as "Flag" and write a query like below.

Flag = IF(Snowy34[Cnt]=1,"True","False")

8. Based on your requirement, if the Flag have true, it is nothing but "perfect order".

Hope it will help for your requirements.

Please refer the link for the snippets.

If you have any queries, please let us know.

[Snowy34]

Just wanna say big thank you to @venal  I have learned some new things! once again thank you so much, mate.

Regards 

Snowy

[venal]

@Snowy34 

Please refer the PBIX.

If you have any concerns, please let us know.

If this post helps, then please consider Accept it as the solution to help the other members find it more
If this post was helpful may I ask you to mark it as solution and click on thumb symbol?

[Anonymous]

I'm assuming that is your raw data but if it's not i'll make two scenarios:

- Raw table : calculated column with a simple if statement like 'Perfect order = if [order qty]=1 and [delivered qty]=1 then true else false

 From here you can pass a visualization filtering by that column results.

If thats a visualization alread you can make the same but in a measure with the same logic.

BR,

DR

[Snowy34]

Good day @Anonymous 

I have tried that but in my case below with the table only one order is a perfect order and that is 1005552. The IF statement gets me only so far it still doesn't provide me with a true answer what order is a PERFECT order, a perfect order must be delivered in full and must be on 1 CO number.

Regards