topic Handling very small numbers in DAX Commands and Tips

Handling very small numbers

user1462 — Wed, 12 Oct 2022 08:56:10 GMT

Hi everyone!

I'm having a problem with the very small numbers in my data;

and the measures I created with them look like this;

I'm trying to get rid of this scientific notation, and I tried some suggestions I've found here in Community, but they didn't solve my problem.

The first suggestion was converting the measure to Currency format, but in that case, there are still a lot of zeros, is there any way that you can share with me to decrease the number of zeros?

And I don't understand why it's between parenthesis? Couldn't remove them either. And on the other hand, there shouldn't be a dollar sign here or any sign actually I just need to see the value only, but if it's not possible to use it without the currency sign, it should be € and when I change it, it returns only 0.

So I really don't know what to do with these tiny values, if you can help me with any solution, Dax formula, or any way to round it for example, I really really appreciate it.

Thank you very much in advance!

Re: Handling very small numbers

PurpleGate — Wed, 12 Oct 2022 13:00:53 GMT

To decrease the number of zeros you would have to multiply the number by 10,000 for example.

To not have to use currency, you could just use Decimal number?

It's in parenthesis because it is a negative number. To remove a negative value you can multiply by -1

Or in your DAX formula you can add

format('value',"0.000")

Re: Handling very small numbers

user1462 — Thu, 13 Oct 2022 04:16:13 GMT

Hello PurpleGate
Thank you very much for the answer!

I've already tried to convert the data type to a decimal number instead of the currency, but this is still how it looks when it's decimal;

so that's still not what I want actually, and the Dax formula you shared with me also gives zero;

Unfortunately, these were not solutions to my problem, and do you have any idea about the euro sign issue? It also returns only 0 when I convert it from dollar to euro

Re: Handling very small numbers

chizuml_ds — Sun, 16 Oct 2022 06:08:12 GMT

Makes no sense

Re: Handling very small numbers

v-jianboli-msft — Mon, 17 Oct 2022 08:55:06 GMT

Hi user1462 ,

Based on your description, I have created a simple sample:

Please try:

Measure = var _a = FORMAT(SUMX('Table',[Column1]),"Scientific") var _b = FIND("-",_a) var _c = VALUE(RIGHT(_a,LEN(_a)-_b)) var _d = FORMAT(SUMX('Table',[Column1]),"General Number") return RIGHT(_d,LEN(_d)-_c-1)

Final output:

Note: the final output is not a number , it is text type, if you want number type, you need to use value() function.

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.