New row creation with performance value distribution

[ohkjuns]

Hello All,

I need help to calculate speed performance column.

This is raw data example. Goal is to create a new table to calculate average speed performance in each step (days per step).

Date	Step	Product lot
9/1/2022	1	Lot1
9/2/2022	2	Lot1
9/3/2022	3	Lot1
9/4/2022	4	Lot1
9/1/2022	1	Lot2
9/2/2022	3	Lot2
9/3/2022	4	Lot2
9/1/2022	1	Lot3
9/2/2022	1	Lot3
9/3/2022	2	Lot3
9/4/2022	4	Lot3

This is what I am looking for. I could not figure out how to add new steps that are not existing in raw data table (i.e. Lot2 step 2). Also, need to distribute speed performance score evenly. 

Product lot	Step	Day per step
Lot1	1	1
Lot1	2	1
Lot1	3	1
Lot2	1	0.5
Lot2	2	0.5
Lot2	3	1
Lot3	1	2
Lot3	2	0.5
Lot3	3	0.5

I could not figure out solution so far. Could anyone help to get proper result?

Thank you in advance. 

[Anonymous]

Hi @ohkjuns ,

Here I suggest you to try this code to create a calculated table.

New Table = 
VAR _GENERATE =
    GENERATE ( VALUES ( 'Table'[Product lot] ), VALUES ( 'Table'[Step] ) )
VAR _ADDCOUNT =
    ADDCOLUMNS (
        _GENERATE,
        "Count",
            CALCULATE (
                COUNT ( 'Table'[Product lot] ),
                FILTER (
                    'Table',
                    'Table'[Product lot] = EARLIER ( [Product lot] )
                        && 'Table'[Step] = EARLIER ( [Step] )
                )
            )
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _ADDCOUNT,
        "MAXNOTBLANK",
            MAXX (
                FILTER (
                    _ADDCOUNT,
                    [Product lot] = EARLIER ( [Product lot] )
                        && [Step] <= EARLIER ( [Step] )
                        && [Count] <> 0
                ),
                [Step]
            )
    )
VAR _ADD2 =
    ADDCOLUMNS (
        _ADD1,
        "Count2",
            VAR _COUNT2 =
                COUNTAX (
                    FILTER (
                        _ADD1,
                        [Product lot] = EARLIER ( 'Table'[Product lot] )
                            && [MAXNOTBLANK] = EARLIER ( [MAXNOTBLANK] )
                    ),
                    [MAXNOTBLANK]
                )
            VAR _MINBLANK =
                MINX (
                    FILTER (
                        _ADDCOUNT,
                        [Product lot] = EARLIER ( [Product lot] )
                            && [Step] >= EARLIER ( [Step] )
                            && [Count] = 0
                    ),
                    [Step]
                )
            VAR _RETURNCOUNT =
                SUMX (
                    FILTER (
                        _ADD1,
                        [Product lot] = EARLIER ( 'Table'[Product lot] )
                            && [MAXNOTBLANK] = EARLIER ( [MAXNOTBLANK] )
                    ),
                    [Count]
                )
            RETURN
                IF (
                    _COUNT2 = 1,
                    [Count],
                    DIVIDE ( _RETURNCOUNT, _MINBLANK - [MAXNOTBLANK] + 1 )
                )
    )
RETURN
    SUMMARIZE(_ADD2,[Product lot],[Step],[Count2])

Result is as below.

Best Regards,
Rico Zhou

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

[amitchandak]

@ohkjuns , what is the logic behind 1, .5 and 2

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[ohkjuns]

Logic for values are days per process. For Lot1, step1-3 takes one day per each step, resulting in 1. For lot2, step 1 and 2 take one day so that it has 0.5 score. Lot 3 needs 2 days for step1, which is 2 score. There is no step4 score yet in the table since it is not sure whether process is completed or not so that it is excluded. 

Hope that this clears your question.

[Anonymous · Accepted solution]

Hi @ohkjuns ,

I have updated my code.

New Table = 
VAR _GENERATE =
    GENERATE ( VALUES ( 'Table'[Product lot] ),CALCULATETABLE( VALUES ( 'Table'[Step] ),FILTER('Table','Table'[Step] <> MAX('Table'[Step]) )))
VAR _ADDCOUNT =
    ADDCOLUMNS (
        _GENERATE,
        "Count",
            CALCULATE (
                COUNT ( 'Table'[Product lot] ),
                FILTER (
                    'Table',
                    'Table'[Product lot] = EARLIER ( [Product lot] )
                        && 'Table'[Step] = EARLIER ( [Step] )
                )
            )
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _ADDCOUNT,
        "MAXNOTBLANK",
            MAXX (
                FILTER (
                    _ADDCOUNT,
                    [Product lot] = EARLIER ( [Product lot] )
                        && [Step] <= EARLIER ( [Step] )
                        && [Count] <> 0
                ),
                [Step]
            )
    )
VAR _ADD2 =
    ADDCOLUMNS (
        _ADD1,
        "Days",
            VAR _COUNT2 =
                COUNTAX (
                    FILTER (
                        _ADD1,
                        [Product lot] = EARLIER ( 'Table'[Product lot] )
                            && [MAXNOTBLANK] = EARLIER ( [MAXNOTBLANK] )
                    ),
                    [MAXNOTBLANK]
                )
            VAR _MINBLANK =
                MINX (
                    FILTER (
                        _ADDCOUNT,
                        [Product lot] = EARLIER ( [Product lot] )
                            && [Step] >= EARLIER ( [Step] )
                            && [Count] = 0
                    ),
                    [Step]
                )
            VAR _RETURNCOUNT =
                SUMX (
                    FILTER (
                        _ADD1,
                        [Product lot] = EARLIER ( 'Table'[Product lot] )
                            && [MAXNOTBLANK] = EARLIER ( [MAXNOTBLANK] )
                    ),
                    [Count]
                )
            RETURN
                IF (
                    _COUNT2 = 1,
                    [Count],
                    DIVIDE ( _RETURNCOUNT, _MINBLANK - [MAXNOTBLANK] + 1 )
                )
    )
RETURN
    SUMMARIZE(_ADD2,[Product lot],[Step],[Days])

Result is as below.

Best Regards,
Rico Zhou

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

[ohkjuns]

It is working good. Thank you for beautiful code. Let me take time to digest details further.