DAX measure to return minimum absolute value while maintaining sign

[egarstad]

Hi All,

I have hit a roadblock with a measure I am trying to create.

I have the following table:

Order	Work Center	Actual Run Time	Expected Run Time
1	A	30	20
2	A	20	20
3	B	15	20
4	B	10	20

I am calculating the time deviation as [Actual Run Time] - [Expected Run Time].

I am trying to create a measure that calculates the smallest absolute time deviation while maintaining the sign.

So for Work Center A the two time deviations are 10 and 0. I want the measure to return 0.

For Work Center B the two deviations are -5 and -10. I want the measure to return -5.

The measure will be displayed in a table visual like this:

Work Center	Min Run Time Deviation
A	0
B	-5

I have created the following measure that calculates the minimum absolute value but I can't figure out how to keep the sign if the time deviation is negative.

Min Run Time Deviation =

MINX ( Table1, ABS ( Table1[Actual Run Time] - Table1[Expected Run Time] ) )

Any advice would be greatly apprecited.

Thanks!

[Greg_Deckler · Accepted solution]

Well, here is one way, but I don't like it because if you have a 5 and a -5 together you will end up with the -5. Not sure if that is OK.

PBIX attached.

[egarstad]

Hi Greg,

The solution you provided works for my use case. The chances of having identical positive and negative deviations is almost 0 as the actual times are recorded in milliseconds. Thanks for the help!