Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have this measure that's causing error due to divide by zero error. How can I get pass this?
Solved! Go to Solution.
Think this should work:
% Change = SUMX ( Summary, DIVIDE( (Summary[CQ] - Summary[PYQ] ), //Numerator Summary[PYQ] , //Denominator "If Error, put something here!" //Alternate Result ) )
use the DIVIDE function instead of /. It has an input as for what to do if there is an error.
Could you help me apply the divide function into the formular above as I'm getting error applying it.
Thanks.
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.
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] ) ) ) )
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |