Forum Discussion
Counting individual orders
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!
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.
23 Replies
- Greg_Deckler
Community Champion
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
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]))?
