Forum Discussion
Divide by Zero error
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
Think this should work:
% Change = SUMX ( Summary, DIVIDE( (Summary[CQ] - Summary[PYQ] ), //Numerator Summary[PYQ] , //Denominator "If Error, put something here!" //Alternate Result ) )
Think this should work:
% Change =
SUMX (
Summary,
DIVIDE(
(Summary[CQ] - Summary[PYQ] ), //Numerator
Summary[PYQ] , //Denominator
"If Error, put something here!" //Alternate Result
)
)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
))
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 ) )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.
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] ) ) ) )