Forum Discussion

maj's avatar
maj
Helper I
5 years ago
Solved

Measure to Ignore Negative Values

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!

 

Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    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.

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

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

    • maj's avatar
      maj
      Helper I
      5 years ago

      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.

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    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.