Do not determine negative numbers during subtraction

[joshua1990]

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?

[SamInogic · Accepted solution]

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 crm@inogic.com

Service:  http://www.inogic.com/services/  

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[SamInogic · Accepted solution]

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 crm@inogic.com

Service:  http://www.inogic.com/services/  

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[joshua1990]

@SamInogic : Thanks a lot for sharing this. From a performance point of view, it this more favorable ?

[SamInogic · Accepted solution]

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 crm@inogic.com

Service:  http://www.inogic.com/services/  

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[FreemanZ]

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).