How to calculate only positive values in column with another column and create new column

[Kasunpathirana]

I have column values both positive and negative for an example lets say this field is "quantity"

Then I have a another field lests say this is "price" I only want to multiply the positive "quanity" value with "price" and create a new column with only positive values.

[TomMartens · Accepted solution]

Hey,

due to the fact, that you want to multiply quantity * price, you have to use a table iterator, to avoid summing prices before the multiplication. In my opinion this measure is what you are looking for:

POSITIVE QUANTITY  = 
CALCULATE(
  SUMX('yourtable'
  , IF('yourtable'[quantity]>0
    ,'yourtable'[quantity] * 'yourtable'[price]
    , BLANK())
  )
)

SUMX iterates over the table, and the IF() check if the quantity is greater than zero.

Hope this helps

[v-yulgu-msft · Accepted solution]

Hi @Kasunpathirana,

You could refer to below formula to create a calculated column in source table:

POSITIVE QUANTITY =
IF ( Table[quantity] > 0, Table[quantity] * Table[price], BLANK () )

Best regards,
Yuliana Gu

[Kasunpathirana]

Thanks for the quick reply i tried the both methods.

But it works only if the colunmns are not calculated columns. Actually my "quantity" field is calculated and the "price" is coming from another table.

I tried to use but since my " quantity"column has formula "quantity" = A-B if this is the case the both functions take the A value and ignore the -B and calculate and give different figure.

e.g.    A= 1545 , B=2516 , quantity = (- 971) then after using both formauls it's giving me = 1545 where as I'm expecting to be 0 as the value.

Also please consider which DAX code should i use if the other column is from another table.

[Kasunpathirana · Accepted solution]

I use a workaround for the I created a new column to take only the positive values and for the function

"quantity" = A-B

and then use the DAX code which was send by @v-yulgu-msft and @TomMartens then it's working in this scenario.

If there is any direct method exists rather than creating a new column. Please let me know. Thanks to @v-yulgu-msft and @TomMartens for their quick response really appreciate.

[v-yulgu-msft · Accepted solution]

Hi @Kasunpathirana,

You could refer to below formula to create a calculated column in source table:

POSITIVE QUANTITY =
IF ( Table[quantity] > 0, Table[quantity] * Table[price], BLANK () )

Best regards,
Yuliana Gu

[TomMartens · Accepted solution]

Hey,

due to the fact, that you want to multiply quantity * price, you have to use a table iterator, to avoid summing prices before the multiplication. In my opinion this measure is what you are looking for:

POSITIVE QUANTITY  = 
CALCULATE(
  SUMX('yourtable'
  , IF('yourtable'[quantity]>0
    ,'yourtable'[quantity] * 'yourtable'[price]
    , BLANK())
  )
)

SUMX iterates over the table, and the IF() check if the quantity is greater than zero.

Hope this helps