Show Qty Completed for Specific Machines

[dkresge]

I have all manufacturing data to make an item on the production floor.  I have Item, Work Center, Operations, hours, and Quantity Complete.  For Item A, it is manufactured on 2 different machines  5568 and 0591 (Machines = Operations). When i complete the work on Operation 1 or machine 0591, I report how many product I made.  For this example I made and reported 202 pieces.  Next, I move to operation 2  or machine 5568 and I add something to the item and compete 202 pieces.  So in total I made 100 finished pieces.  If I pull in the operations/machines in my report with the quantites I made, it shows 404 pieces.  I know it is summing the quantity complete. However, I want the quantity completed to only show on the second machine 5568 line.  So the qty on the first operation/machine 0591 should be 0 and athe qty for the 2nd operation/machine 5568 should be 202.  How do I write a Dax expression to get the results I need?   

How the report currenty looks:

Production Order	Machine	Operation	Item	Qty Completed
C888	0591	10	A	202
C888	5568	20	A	202
Total				404

How I want it to look:

Production Order	Machine	Operation	Item	Qty Completed
C888	0591	10	A	0
C888	5568	20	A	202
Total				202

Thanks in advance for any help provided.

[v-alq-msft · Accepted solution]

Hi, @dkresge 

You may try the following measure. The pbix file is attached in the end.

Re = 
SUMX(
    ADDCOLUMNS(
        'Table',
        "Result",
        IF(
            [Operation]=
            CALCULATE(
                MAX('Table'[Operation]),
                ALLEXCEPT('Table','Table'[Production Order])
            ),
            [Qty Completed],
            0
        )
    ),
    [Result]
)

Result:

If you want the result is classified by the specific machine. You may try the following measure.

Re =
SUMX (
    ADDCOLUMNS (
        'Table',
        "Result",
            IF (
                [Operation]
                    = CALCULATE (
                        MAX ( 'Table'[Operation] ),
                        FILTER (
                            ALLEXCEPT ( 'Table', 'Table'[Production Order] ),
                            'Table'[Machine] = "095568"
                        )
                    ),
                [Qty Completed],
                0
            )
    ),
    [Result]
)

If I misunderstand your thoughts, please show us with some sample data and expected result by OneDrive for Business. Do mask sensitive data before uploading. Thanks.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlB]

@dkresge 

I don't think it will be difficult to implement, but I haven't quite understood what the requirement is. Please try to explain it a bit more with some more data, with several production values and several machines. And show the expected result.  What is WorkCenter? I don't see it in your data.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @dkresge 

Where in your data can we see which one the first machine is, which one the second and, more importantly, how do we see what machines actually add new pieces or only modify them?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[dkresge]

Hello,

I do have an operation number that will let me know what machine is first or second.  I added the operation to the tables above to identify 1st (10) or 2nd (20). But the qty total I would like to see is classified by the specific machine not the final operation.  

Cound i use something like this for the dax formula? If i need more than one work center could i just continue to add FILTER?

qty = CALCULATE(SUM(DirectLabor[QuantityCompleted]), FILTER(DirectLabor, DirectLabor[WorkCenter]="095568"))