Measure to calculate but ignore if row result of calculations less than zero and aggregate the rest

[Sully7518]

Hi, need help with the following please. My data comes to me in the below layout (I have simplified it for this forums purpose):

I have a query which returns a table like the one below:

Batch Number	Batch Stage	Type of Time	Qty Time (minutes)
ABCD11111	Mixing	Actual Duration	100
ABCD11111	Mixing	Planned Duration	50
ABCD22222	Mixing	Actual Duration	200
ABCD22222	Mixing	Planned Duration	300
AAAA00000	Mixing	Actual Duration	60
AAAA00000	Mixing	Planned Duration	50
ABBB99999	Stiring	Actual Duration	100
ABBB99999	Stirring	Planned Duration	110
CCCC10000	Stirring	Actual Duration	120
CCCC10000	Stirring	Planned Duration	110

What I need is a measure which calculates each Batch Actual vs each Batch Planned (from the type of Time Column) on a row by row individual basis. It then needs to ignore/zero any calculation which return less than zero result and only aggregate the one with a positive result.

The calcuation would be:  Actual Duration - Planned Duration = Net Lost time

This is what it would look like if it was in the correct form.

Batch Number	Batch Stage	Actual Duration	Planned Duration	Sum Result	Action
ABCD11111	Mixing	100	50	50	Keep
ABCD22222	Mixing	200	300	-100	Ignore/zero
AAAA00000	Mixing	60	50	10	Keep
ABBB99999	Stirring	100	110	-10	Ignore/zero
CCCC10000	Stirring	120	110	10	Keep

If I then put the following into a Bar Chart = Y - Axis [Batch Stage] and X-Axis [Sum Result - i.e. the measure I am asking for] 

The result would return the below - 

Mixing Lost Time =  60

Stirring Lost Time = 10

I would really appreciate someones help. Changing the layout of the data would be difficult with the amount of records.

Many thanks in advance.

Sully.

[Sully7518]

Hi @FreemanZ ,

Fanstastic, it worked perfectly!! You're an absolute star. Thank you so much for taking the time to help me, I really appreciate it!

[FreemanZ · Accepted solution]

hi @Sully7518 

try to plot a visual with the [Batch Stage] column and a measure like:

LostTime = 
VAR _table =
ADDCOLUMNS(
    SUMMARIZE(
        TableName,
        TableName[Batch Number],
        TableName[Batch Stage]
    ),
    "SumResult",
    CALCULATE(
        MINX(
            FILTER(TableName, TableName[Type of Time] = "Actual Duration"),
            TableName[Qty Time (minutes)]
        ) -
        MINX(
            FILTER(TableName, TableName[Type of Time] = "Planned Duration"),
            TableName[Qty Time (minutes)]
        )  
    )
)
RETURN
SUMX(
    FILTER(_table, [SumResult]>0),
    [SumResult]
)

it worked like: