Forum Discussion
Post ProdigyDo not determine negative numbers during subtraction
Hi all!
I have a simple calculation that substracts the amount A vs amount b
[Amount A] - [Amount B]
But here I would like to exclude all negative numbers. If the result is below 0, I would like to get a 0 instead of -500,
For that purpose I have a IF statement:
IF([Amount A] - [Amount B] < 0, 0, [Amount A] - [Amount B]).
Is there any more effcient / professional way?
Hi,
Power BI DAX have SIGN function which returns 1,0 or -1 value based on the number is Positive, 0 or Negative respectively.
So, you can use below formula to check if the number is negative then display 0 otherwise display the difference.
Difference = IF(SIGN('Sample Tab'[Post Value] - 'Sample Tab'[Pre Value]) = -1, 0 , 'Sample Tab'[Post Value] - 'Sample Tab'[Pre Value])
Please refer to the below screenshot for expression and output for the same.
If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.
Thanks!Inogic Professional Services
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email at [email protected]
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
Hi,
This expression is noncomplex subtraction DAX, so it should not affect the performance of the Power BI report. Also, this expression with Sign() function provided by Power BI DAX seems more efficient in development perspective.
Thanks!
Inogic Professional Services
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email at [email protected]
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
4 Replies
FreemanZSuper User
Hi joshua1990
use a variable for [Amount A] - [Amount B] would be slightly better, as the subtraction takes place once instead of twice.
Like this:
Measure =
VAR _value = [Amount A] - [Amount B]
RETURN IF(_value< 0, 0, _value).
- SamInogic
Hi,
Power BI DAX have SIGN function which returns 1,0 or -1 value based on the number is Positive, 0 or Negative respectively.
So, you can use below formula to check if the number is negative then display 0 otherwise display the difference.
Difference = IF(SIGN('Sample Tab'[Post Value] - 'Sample Tab'[Pre Value]) = -1, 0 , 'Sample Tab'[Post Value] - 'Sample Tab'[Pre Value])
Please refer to the below screenshot for expression and output for the same.
If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.
Thanks!Inogic Professional Services
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email at [email protected]
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
joshua1990SamInogic : Thanks a lot for sharing this. From a performance point of view, it this more favorable ?
- SamInogic
Hi,
This expression is noncomplex subtraction DAX, so it should not affect the performance of the Power BI report. Also, this expression with Sign() function provided by Power BI DAX seems more efficient in development perspective.
Thanks!
Inogic Professional Services
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email at [email protected]
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/