Divide by Zero error

[Anonymous]

Hi All,

I have this measure that's causing error due to divide by zero error.  How can I get pass this?

% Change =  SUMX(Summary, Summary[CQ] - Summary[PYQ]/(Summary[PYQ]))

Thanks

Ejyks

[Anonymous]

use the DIVIDE function instead of /.  It has an input as for what to do if there is an error. 

[Anonymous]

Could you help me apply the divide function into the formular above as I'm getting error applying it.

Thanks.

[Anonymous · Accepted solution]

Think this should work:

% Change =
SUMX (
    Summary,
    DIVIDE( 
    	(Summary[CQ] - Summary[PYQ] ),  //Numerator
        Summary[PYQ] ,                  //Denominator
        "If Error, put something here!" //Alternate Result
	)
)

[Anonymous]

This worked but I'm having -100% where 0 is dividing a number . How can I make thi sto be 0% instead of -100%

This is what I used.

% Change PYQ = SUMX(Summary,

                DIVIDE (Summary[CQ] - Summary[PYQ], //Numerator

                        Summary[PYQ]              //Denominator

                     ))

[Anonymous]

Without seeing exactly what you are talking about, what if you tried this:

% Change =
SUMX (
    Summary,
    DIVIDE( 
    	(Summary[CQ] - Summary[PYQ] ),  //Numerator
        Summary[PYQ] ,                  //Denominator
        0 //Alternate Result
	)
)

[Anonymous]

I couldnt screen shot my table here but it shows like this

              % Change

                  -4%

                 -3%

             -100%

-100% being the one I want to show 0% and there's no change after I applied the latest formula.

[Anonymous]

Let's try this one.  Basically added in logic that if either the absolute values of the values we need to divide is  0, then we want a 0 there, if not to the normal divide.  

Just need to substitute out my generic table and measures for yours:

% Change = 
SUMX( 
    VALUES( Table1[Index] ),
    CALCULATE(
    IF (
        OR ( 
            ABS([Total Item1] ) = 0,
            ABS( [Total Item 2]) = 0
        ),
        0,            
         DIVIDE( 
            ( [Total Item 2] - [Total Item1]),
            [Total Item1]
        )
    )
    )
)  

[Anonymous]

Basically I got confused by the your last formula so I changed the whole formula to use an IF statement and it now works for me. The -1.0 is what gives -100% when converted to percentage.

% Change PYQ = IF(DIVIDE(SUM(Summary[CQ])- SUM(Summary[PYQ]), //Numerator

                   SUM(Summary[PYQ])                     //Denominator

                   ) = -1.0, 0,

DIVIDE(SUM(Summary[CQ])- SUM(Summary[PYQ]), //Numerator

                   SUM(Summary[PYQ])))