cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

SQRT problem in DAX/excel - too many decimal places?

Hi I am having trouble with sqrt in a dax measure in Excel and would be grateful for assistance.

 

When I try to place SQRT meaure in pivot table I receive following message.

 

Capture.PNG

 

The data I am working on all looks fine (and I have recreated calcs manually and they are ok) - though i was wondering if decimal places are causing the issue and if so how do I restrict these. An example of the Standard Error result I want the SQRT of looks like:

Capture2.PNG

The actual number is correct - though maybe too big? How do I reduce? or could something else be responsible? I have tried editing the measure settings and although I set 5 decimal places, the underlying number does not change (see formula bar below)

Capture3.PNG

Many thanks,

 

A

 

1 ACCEPTED SOLUTION

Hi @adavid999,

 

From the measure and error you provided I would actually suspect your issue isn't the rounding of the measure itself but the fact the result of the measure is too small ie. 0.000000000000000000000000000000000000000 (and more thousands of zeros) 0001 or too large ie. infinity. In technical terms this is called numerical underflow/overflow

 

Have you checked to make sure that in your measure the numerator or denominator doesn't evaluate in such a way that one is very small and one is very large?

 

Kris

View solution in original post

7 REPLIES 7
Super User II
Super User II

@adavid999 

Maybe try putting a ROUND () on the measure and setting it to 10 or 15 digits?

thanks @jdbuchanan71 that didn't work. I get a similar errorCapture.PNG

Do you know if there is a way of setting the decimal places when creating the measure?

 

I am assuming that this is the problem btw - I can't think of anything else that might be causing it. I will restart my computer just in case!

A

Hi @adavid999,

 

From the measure and error you provided I would actually suspect your issue isn't the rounding of the measure itself but the fact the result of the measure is too small ie. 0.000000000000000000000000000000000000000 (and more thousands of zeros) 0001 or too large ie. infinity. In technical terms this is called numerical underflow/overflow

 

Have you checked to make sure that in your measure the numerator or denominator doesn't evaluate in such a way that one is very small and one is very large?

 

Kris

View solution in original post

Just for info - this came up again and I think your response was the answer @kris .

I wasn't using the safe DIVIDE measure (I didn't know it existed), all my measures to calculate standard error and SQRT were using "/". Switching to DIVIDE function seems to have solved the problem.

Cheers,

A

Hi @adavid999,

 

Great news! Yes I'm always cautious when calculating variances as it only takes one calculation to have a very small (or zero) denominator and the number it returns can be huge or even infinity. If you suspect this is ever going to be the case the DIVIDE function is great 🙂! Only note is that there could be a slight performance hit as there's some extra work for the engine to do to resolve these math errors or under/overflows, but this is only noticable when datasets get huge. Links below

 

 

Kris

Thanks @kriscoupe I don't think it's that but playing around I think it might be whenever I load any new measure to this partcular pivot table it's loading as a % and then I have reformat on the measure value settings to ensure it is a number. So the default is % - any idea how to change that?

 

I'm sure it's something simple - it's not something I can do from within the 'edit measure dialogue box'. It doesn't seem to be something I can change from within the pivot table field settings or options, either.

 

Update

I have started the pivot table again and the problem disappears so something to do with pivot table auto-formatting new measures to %!

Interesting, yes maybe something was "stuck" (for lack of a better term) on that specific Pivot Table. The turn it off and back on again approach works wonder sometimes hey 😀.

 

And not sure why it would auto format to percentage I'm afraid but it's good to hear everything is sorted now!

 

Kris

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors