Measure to Ignore Negative Values

[maj]

I have the following measure:

Cases to Target Actual = (DOS Target - MIN DOS) * Max Daily Forecast

DOS Target is a parameter

MIN DOS is a measure

Max Daily Forecast is a measure

In the top table, which looks at item/DC combination, I want the expected results as shown, including the negative numbers (note the numbers are a bit off due to rounding). However, in the bottom table, which looks at item only, I want to ignore the negatives and see a total of the positive numbers only.

In this example, I would want the bottom to show 4.81 only (3.62+1.19).

I tried the following IF statement:

IF(('DOS Target'[DOS Target Value] - [MIN DOS]) * [MAX Daily Forecast (Cases)] < 0, 0, ('DOS Target'[DOS Target Value] - [MIN DOS]) * [MAX Daily Forecast (Cases)])

However, when I use that formula in the bottom table (item only), if the summed total is a negative number, it returns 0 instead of summing the positive values only.

Is anyone able to help me with a measure that calculates this properly? Thanks!

[tenm677]

Try this..this worked for me.

https://stackoverflow.com/questions/68837422/getting-measure-to-only-show-positive-values-in-matrix-...

[v-xulin-mstf · Accepted solution]

Hi @maj,

You can try measure as:

Measure 2 = 
IF(
    HASONEVALUE('DOS Target'[DOS Target Value] ),
    IF(('DOS Target'[DOS Target Value] - [MIN DOS]) * [MAX Daily Forecast (Cases)] < 0, 0, ('DOS Target'[DOS Target Value] - [MIN DOS]) * [MAX Daily Forecast (Cases)]),
    IF(
        ('DOS Target'[DOS Target Value] - [MIN DOS]) * [MAX Daily Forecast (Cases)]>0,
        SUM(TOTAL),
        0
        )

You can refer this:

https://docs.microsoft.com/en-us/dax/hasonevalue-function-dax

Best Regards,

Link

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

[Greg_Deckler]

@maj Can't you just use the Filter pane and specify "Cases to Target Actual" is greater than 0?

[maj]

No, because if the summed total is a negative number, it would get filtered out.  Consider the following example:

These would be the results in the top table (item/DC combination):
5

-10

2

-3

In the second table (item only), the option you suggested would filter out this item.  I really want it to return a value of 7.