Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Think this should work:

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

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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

Anonymous
Not applicable

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

Thanks.

Anonymous
Not applicable

Think this should work:

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

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
Not applicable

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
Not applicable

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
Not applicable

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

Divide look for errors, put zero.png

Anonymous
Not applicable

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.