cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
will1329 Frequent Visitor
Frequent Visitor

SQRT error on PowerBI/PowerPivot when using column references

Hi,

 

I am attempting to do a workaround for the lack of NORMSINV function in PowerBI/PowerPivot. Part of my formula is:

B = 1.96*SQRT(1.96^2+4*[NUMERATOR]*(1-[NUMERATOR]/[DENOMINATOR]))

 

When NUMERATOR = 1 and DENOMINATOR = 1, I should get a result of 3.84.

 

However, in PowerBI/PowerPivot this result in the error:

An argument of function 'SQRT' has the wrong data type or the result is too large or too small.

 

The error is not present when using the exact same formula in excel and also goes away when I replace the column references with values (i.e. if I replace both NUMERATOR and DENOMINATOR with 1 I get the desired result).

 

Does anyone know why this might be happening?

 

Many thanks,

 

Will

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: SQRT error on PowerBI/PowerPivot when using column references

The documentation on SQRT states that it returns an error for negative numbers. You are most likely getting negative numbers passed to the SQRT function. To test, try creating this column:

 

D = VAR myvalue = [NUMERATOR]/[DENOMINATOR]
    VAR myvalue2 = 1.96^2
    VAR myvalue3 = myvalue2+4*[NUMERATOR]*(1-myvalue)
    RETURN myvalue3
//    RETURN 1.96*SQRT(myvalue3)

If you see negative numbers, you have your answer. See documentation here:

 

https://msdn.microsoft.com/en-us/library/ee634559.aspx

 

Note the Remarks section.

 

You could potentially fix this by wrapping the inside of the SQRT with an ABS. You could create a VAR to see if the value that you are taking the SQRT of is negative and, if so, multiply by -1 outside of the SQRT if you really need the negatives.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Dave1 Regular Visitor
Regular Visitor

Re: SQRT error on PowerBI/PowerPivot when using column references

Hi, 

 

The issue is caused by negative results for SQRT function.

Create a measure for your numerator = sum(YourNumerator)

Create a measure for your denominator =  sum(YourDenominator)

 

Test logic

    Measure = Measure = 1.96*SQRT(1.96^2+4*1*(1-1/1))    === 3.84

Prod Code

    Measure2 = Calculate(1.96*SQRT(ABS(1.96^2+4*[sum1]*(1-[sum1]/[Sum2]))))

 

Measure and Measure 2 produce the same result for Field A, where Numerator and denominator are 1.

demo1.PNG

View solution in original post

3 REPLIES 3
Super User
Super User

Re: SQRT error on PowerBI/PowerPivot when using column references

The documentation on SQRT states that it returns an error for negative numbers. You are most likely getting negative numbers passed to the SQRT function. To test, try creating this column:

 

D = VAR myvalue = [NUMERATOR]/[DENOMINATOR]
    VAR myvalue2 = 1.96^2
    VAR myvalue3 = myvalue2+4*[NUMERATOR]*(1-myvalue)
    RETURN myvalue3
//    RETURN 1.96*SQRT(myvalue3)

If you see negative numbers, you have your answer. See documentation here:

 

https://msdn.microsoft.com/en-us/library/ee634559.aspx

 

Note the Remarks section.

 

You could potentially fix this by wrapping the inside of the SQRT with an ABS. You could create a VAR to see if the value that you are taking the SQRT of is negative and, if so, multiply by -1 outside of the SQRT if you really need the negatives.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Dave1 Regular Visitor
Regular Visitor

Re: SQRT error on PowerBI/PowerPivot when using column references

Hi, 

 

The issue is caused by negative results for SQRT function.

Create a measure for your numerator = sum(YourNumerator)

Create a measure for your denominator =  sum(YourDenominator)

 

Test logic

    Measure = Measure = 1.96*SQRT(1.96^2+4*1*(1-1/1))    === 3.84

Prod Code

    Measure2 = Calculate(1.96*SQRT(ABS(1.96^2+4*[sum1]*(1-[sum1]/[Sum2]))))

 

Measure and Measure 2 produce the same result for Field A, where Numerator and denominator are 1.

demo1.PNG

View solution in original post

will1329 Frequent Visitor
Frequent Visitor

Re: SQRT error on PowerBI/PowerPivot when using column references

Hi,

 

Thanks for the help both. Thought I'd made sure there were no negatives but a few had slipped past as the records contained blanks (12 out of 15million isn't too bad I think!). I've added your useful suggestions to deal with negatives for extra certainty. Just goes to show you should check your data before you analyse it

 

Thanks

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 1,035 guests
Please welcome our newest community members: