Counting individual orders

[Anonymous]

Dear dax expert,

The below mentioned sheet displays the amount of production orders per line, per supervisor.

Unfortunately the productIDs are not solely individual orders.

A productionID is considered unique when the current row has a different subnummer (different packaging) than the previous productionID.

Secondly a productionID is only considered unique when it has been produced on the same line by the same supervisor.

When the amount of workers (aantal mensen) has changed, but the subnummer hasn't this means that it is the same order as the previous order. Sometimes nor the subnummer, nor the amount of people (aantal mensen) has changed, then perhaps something went wrong upon registration in SQL.

Herebelow I drew a few examples that represent individual orders.

All help is welcome and appreciated!

[Greg_Deckler]

Going to need to play around with this, need sample data that can be copied and pasted.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi @Greg_Deckler,

Herebelow the sample data set,

Aansturing Lijn ProductieID Subnummer Aantal mensen Begintijd def Eindtijd def
Nikoleta 1 6747 300-890 12,00 17-9-2018 12:54:43 17-9-2018 13:55:33
Nikoleta 1 6748 300-320 12,00 17-9-2018 13:56:01 17-9-2018 14:47:09
Nikoleta 1 6749 300-1180 2,00 17-9-2018 7:36:11 17-9-2018 10:50:34
Nikoleta 1 6750 300-1250 1,00 17-9-2018 7:36:11 17-9-2018 9:20:29
Gabriela 1 6751 300-381 13,00 17-9-2018 14:54:09 17-9-2018 15:49:17
Gabriela 1 6752 300-381 13,00 17-9-2018 15:49:29 17-9-2018 19:39:54
Gabriela 1 6753 300-381 13,00 17-9-2018 19:42:42 17-9-2018 21:24:46
Gabriela 1 6754 300-381 13,00 17-9-2018 21:24:58 17-9-2018 22:49:49
Nikoleta 2 6755 300-315 11,00 17-9-2018 5:58:09 17-9-2018 7:31:18
Nikoleta 2 6756 300-320 11,00 17-9-2018 7:31:28 17-9-2018 8:28:15
Nikoleta 2 6757 300-320 11,00 17-9-2018 8:28:15 17-9-2018 9:05:44
Nikoleta 2 6758 300-320 10,00 17-9-2018 9:05:59 17-9-2018 9:36:43
Nikoleta 2 6759 300-1210 10,00 17-9-2018 9:37:44 17-9-2018 11:24:46
Nikoleta 2 6760 300-320 10,00 17-9-2018 11:25:18 17-9-2018 14:54:52
Maria 3 6761 300-381 12,00 17-9-2018 5:47:00 17-9-2018 7:09:18
Maria 3 6762 300-381 11,00 17-9-2018 7:09:42 17-9-2018 8:12:29
Maria 3 6763 300-721 11,00 17-9-2018 8:16:16 17-9-2018 8:30:34
Maria 3 6764 300-721 11,00 17-9-2018 8:30:34 17-9-2018 9:10:05
Maria 3 6765 300-745 14,00 17-9-2018 9:10:51 17-9-2018 9:27:05
Maria 3 6766 300-745 13,00 17-9-2018 9:27:59 17-9-2018 10:05:56
Maria 3 6767 300-745 13,00 17-9-2018 10:06:23 17-9-2018 10:49:17
Maria 3 6768 300-1285 13,00 17-9-2018 10:59:02 17-9-2018 11:20:57

[Greg_Deckler]

Can you just do a:

Measure = COUNTROWS(SUMMARIZE('Table',[Subnummer]))

?

[Anonymous]

Hi @Greg_Deckler,

That's definitely part of the solution.  However, a row only counts as a unique order when:

- The current row (productieID) does not have the same subnummer as the previous productieID

- The same subnummer on a different production line (lijn) means 2 different orders (as you may understand)

I have highlighted a few examples of orderlines that together are one order. 

If possible and if you would know a solution, would it be possible to sum up the amount of products that belongs to the same order?

The formula of the amount of packages is stated herebelow.

Appreciate your help.

Flowpacks geproduceerd totaal Maasland = sum('RegistratieMaasland'[AantalFlowpacks])

[Anonymous]

@Greg_Deckler Friendly reminder 🙂

[Anonymous]

Would there be someone to help me solving this issue?

[Anonymous]

Read your post. Here is what I understood.

1. You want to sum the quantity based on each unique production order.
2. Though there is no production order number, any unique combination of the following fields is to be treated as a unique production order.
   1. Supervisor
   2. Production Line Number
   3. Subnummer
3. Production ID need not be considered as the same Subnummer can have multiple production IDs and therefore it does not matter if the production ID is same or different.

Is my understanding correct? Or is there anything else that has to be considered for "Unique Production Order"?

I will try to help if you can make me understand the concept.

[Anonymous]

Hi @Anonymous,

Thats right, there is no production order number. The printscreen I made shows 3 examples of unique orders.

All of your understandings were correct.

I would like to know the amount of unique production orders, and if possible the sum of quantity based on each unique production order.

Your help is much appreciated.

[Anonymous]

In your screenshot, there was no scenario where there is a different supervisor for the same Subnummer or different production line for the same subnummer. But you have mentioned that if there is a different supervisor or different production line number, it has to be treated as a unique order.

As a solution to your case, can you create a calculated column by concatenating the values in Supervisor, Production Line Number, and Subnummer and treat the concatenated field as a unique production order. Then sum up the quantities based on this concatenated field?

Can you just try this and post your feedback. We will find a more optimal solution, but once you try concatenating the 3 fields and sum up the quantities based on this concatenated field, we can affirm that the logic is correct and you are getting the correct result.

[Anonymous]

Hi @Anonymous,

Herebelow the outcomes. I figured that supervisor doesnt do the trick, therefore I used shift instead.

[Anonymous]

Hi,

Are you getting the correct results?

[Anonymous]

@Anonymous

They are correct indeed. 

Would it be possible to write some DAX so that 1d300-1050 is 1 row with the higlighted start- and endtime, summing up the highlighted quantities?

[Anonymous]

It's possible. Basically you can create a calculated table using DISTINCT on the unique id you created with concatenation. Then add the start time using MINX on the starttime field, endtime using MAXX and SUMX on quanitities. While adding these columns, you have to base it on the unique id using EARLIER function. Presently I am replying on a mobile phone and don t have computer with me. You can try doing this , i will send you the formula by EOD.

[Anonymous]

Much appreciated, I will wait for your answer and try to understand the logic meanwhile.

[v-piga-msft]

Hi @Anonymous,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[Anonymous]

Hi @Anonymous,

If my solution helped you don't forget to click the Thumbs Up/Kudos button. 🙂

[Anonymous · Accepted solution]

Hi,

Assuming that the table name of the screenshot in your last post is Production and you want to summarize the production in a new table called "ProductionOrderSummary", the following are the DAX codes.

Step 1: Create a calculated table called ProductionOrderSummary

ProductionOrderSummary = DISTINCT(Production[UniqueProductionOrder])

Step 2: Add the start time column.

Add a calculated column to ProductionOrderSummary table you have created with the following code.

StartTime = MINX(FILTER(ALL(Production),Production[UniqueProductionOrder]=EARLIER(ProductionOrderSummary[UniqueProductionOrder])),Production[Begintijd def])

Step 3: Add the end time column.

Add a calculated column to ProductionOrderSummary table you have created with the following code.

EndTime = MAXX(FILTER(ALL(Production),Production[UniqueProductionOrder]=EARLIER(ProductionOrderSummary[UniqueProductionOrder])),Production[Eindtijd def])

Step 4: Add the total quantity

Add a calculated column to ProductionOrderSummary table you have created with the following code.

Quantity = SUMX(FILTER(ALL(Production),Production[UniqueProductionOrder] = EARLIER(ProductionOrderSummary[UniqueProductionOrder])),Production[Qty])

Please change the table names and field names in the codes above as applicable in your case.

[Anonymous]

Thanks so much, understand the logic now : )

[Anonymous]

@Anonymous,

The UniqueProductionOrder is 

UniekeOrder = 
CONCATENATE(RegistratieMaasland[Lijn]&RegistratieMaasland[Shift]&"  ";RegistratieMaasland[Subnummer]&"     "&RegistratieMaasland[Datum])

- Line number

- Shift number

- Subnumber

- Datenumber

What happens next is that the start time and end time are incorrect when an order switches between midnight. Do you have a solution for this?

[Anonymous]

Didnt include the second picture