Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
98 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |