Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
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.
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.
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.
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
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
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |