Gross Margin after dividing returns enormous number

[Justas]

Hello,

I use dax Gross profit margin =Divide (Gross profit, Income)*100 formula. My income is zero in Month of May. After division returns enormous number. 

[dedelman_clng]

Hi @Justas -

DIVIDE has a 3rd parameter, which you set the value if the result would otherwise throw an error.

Gross profit margin =Divide (Gross profit, Income, 0)*100

Hope this helps

David

[Justas]

Nothing chages if I put alternate rezult 0 in Divide formula

[dedelman_clng]

Sorry, I read the question wrong.

Check your data that Income is actually 0.  Often times when things are sourced from Excel or other financial applications, what looks to be 0 will be something like 1.2e-6 (.00000012) which rounds to 0 in 2 decimal places but isn't 0.

In your case Income would be approx 4.5e-7 (0.000000045) based on the results shown.

Hope this helps

David

[Justas]

In excel Credit-Debit shows 0,00E+00, but then calculates margin denominator (income) seems to be not equal to zero. I do not know what to do!

[dedelman_clng · Accepted solution]

Try using the ROUND function on your fields before dividing.  Or alternatively do the rounding in Power Query so they're stored with the right number of significant digits.

[Justas]

My new formula:

Gross profi margin %=Divide([Gross Profit],round([Revenue],2))*100

[Justas]

In excel mannualy made decimal places to check if any other numbers after comma not equal to 0.  Income calculates Credit-Debit and result from operatios equals to zero (see difference below):

Is any formula or method in excel to check that data is really 0?

[Justas]

In  Query I found last decimal space with number 7, but in excel shows 8873,2000000000. What to do 😞 ?