Need help calculating total changeover time per supervisor

[Anonymous]

Dear DAX experts,

In a previous thread I calculated (with the help of Dale) the total changeover time between production orders with the following dax:

Omsteltijden in minuten = 
VAR nextProductID =
    CALCULATE (
        MIN ( RegistratieMaasland[ProductieID] );
        FILTER (
            ALL ( RegistratieMaasland );
            RegistratieMaasland[Aansturing] = MIN ( RegistratieMaasland[Aansturing] )
                && RegistratieMaasland[Lijn] = MIN ( RegistratieMaasland[Lijn] )
                && RegistratieMaasland[ProductieID] > MIN ( RegistratieMaasland[ProductieID] )
        )
    )
VAR endTime =
    MIN ( RegistratieMaasland[Eindtijd def] )
VAR nextStartTime =
    CALCULATE (
        MIN ( RegistratieMaasland[Begintijd def] );
        FILTER ( ALL ( RegistratieMaasland ); RegistratieMaasland[ProductieID] = nextProductID )
    )
RETURN
    DATEDIFF ( endTime; nextStartTime;MINUTE )

This had the following impact on the outcomes:

Then we created a filter to filter out invalid outcomes (such as shift changes, or faulty inserts). Rows with a "1" are valid, and the "0" stands for invalid. I used the following DAX:

Valid outcome = 
SUMX (
    'RegistratieMaasland';
    IF (
        'Measures omsteltijden'[Omsteltijden in minuten] >= 0
            && 'Measures omsteltijden'[Omsteltijden in minuten] <= 30
            && ISBLANK ( 'Measures omsteltijden'[Omsteltijden in minuten] ) = FALSE ();
        1;
        0
    )
)

Now finaly I would like to know the total changeover time (in minutes) per supervisor (aansturing), only for the valid outcomes.

All help is appreciated,

[v-danhe-msft]

Hi @Anonymous,

I have modified your [Valid outcome] measure as this:

Valid outcome = 
SUMX (
    'Measures omsteltijden',
    IF (
        'Measures omsteltijden'[Omsteltijden in minuten] >= 0
            && 'Measures omsteltijden'[Omsteltijden in minuten] <= 30
            && ISBLANK ( 'Measures omsteltijden'[Omsteltijden in minuten] ) = FALSE (),
        [Omsteltijden in minuten],
        0
    )
)

And I have created a new measure to calculate the [changeover time per supervisor]:

changeover time per supervisor = AVERAGEX('Measures omsteltijden','Measures omsteltijden'[Valid outcome])

Result:

You could also download the pbix to have a view:

https://www.dropbox.com/s/5jnwlwpdza0r61r/Need%20help%20calculating%20total%20changeover%20time%20per%20supervisor.pbix?dl=0

Regards,

Daniel He

[Anonymous]

Hi @v-danhe-msft,

Thanks for helping me.

In your picture there is one invalid count, which is 125 minutes. This has probably been a stop and not a changeover. 

Would you be able to modify the formula so that all 'omsteltijden in minuten' <0 and >30 are BLANK as a valid outcome. In this way they are excluded as part of the average.

Best,

Luuk

[v-danhe-msft]

Hi @Anonymous,

From your description, I have modified my formula:

Valid outcome = 
SUMX (
    'Measures omsteltijden',
    IF ([Omsteltijden in minuten]=BLANK(),0,
        IF(
        'Measures omsteltijden'[Omsteltijden in minuten] >= 0
            && 'Measures omsteltijden'[Omsteltijden in minuten] <= 30
            && ISBLANK ( 'Measures omsteltijden'[Omsteltijden in minuten] ) = FALSE (),
        0,
        [Omsteltijden in minuten]
        )
    )
)

changeover time per supervisor = AVERAGEX('Measures omsteltijden','Measures omsteltijden'[Valid outcome])

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

Hi @v-danhe-msft,

I have slightly modified the formula to:

Valid outcome = 
SUMX (
    RegistratieMaasland;
     IF( [Omsteltijden in minuten] >=0
            && [Omsteltijden in minuten] <= 30;
            [Omsteltijden in minuten];
        BLANK()
     ))

Yet I have another request regarding the valid outcome.

I have added the "subnummer" to the dataset. The subnummer is like a different packaging used for different customers. So basically when the subnummer has changed it means a change over is valid. Sometimes only the amount of people on a line has changed (typically when a person becomes ill or for different purposes), this will be registered but does not mean a changeover has been conducted. 

I have highlighted an example for which the outcome is valid.

The change from production order 6070 to 6071 is an example of an invalid outcome, here the amount of people has changed and I don't want the outcome to be computed (the cell shall be blank).

Would you be able to modify the formula with the above-mentioned request? Thanks in advance!

[Anonymous]

Hi @v-danhe-msft, sorry for bothering but did you find a solution?

[v-danhe-msft]

Hi @Anonymous,

From your description, I have modified my formula:

Valid outcome = 
SUMX (
    'Measures omsteltijden',
    IF ([Omsteltijden in minuten]=BLANK(),0,
        IF(
        'Measures omsteltijden'[Omsteltijden in minuten] >= 0
            && 'Measures omsteltijden'[Omsteltijden in minuten] <= 30
            && ISBLANK ( 'Measures omsteltijden'[Omsteltijden in minuten] ) = FALSE (),
        0,
        [Omsteltijden in minuten]
        )
    )
)

changeover time per supervisor = AVERAGEX('Measures omsteltijden','Measures omsteltijden'[Valid outcome])

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He