topic Re: Gross Margin after dividing returns enormous number in DAX Commands and Tips

Gross Margin after dividing returns enormous number

Justas — Mon, 21 Jun 2021 12:29:31 GMT

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.

Re: Gross Margin after dividing returns enormous number

dedelman_clng — Mon, 21 Jun 2021 12:34:31 GMT

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

Re: Gross Margin after dividing returns enormous number

Justas — Mon, 21 Jun 2021 12:48:28 GMT

Nothing chages if I put alternate rezult 0 in Divide formula

Re: Gross Margin after dividing returns enormous number

dedelman_clng — Mon, 21 Jun 2021 12:54:17 GMT

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

Re: Gross Margin after dividing returns enormous number

Justas — Tue, 22 Jun 2021 05:12:46 GMT

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?

Re: Gross Margin after dividing returns enormous number

Justas — Tue, 22 Jun 2021 11:01:07 GMT

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!

Re: Gross Margin after dividing returns enormous number

Justas — Tue, 22 Jun 2021 12:20:16 GMT

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

Re: Gross Margin after dividing returns enormous number

dedelman_clng — Tue, 22 Jun 2021 12:14:00 GMT

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.

Re: Gross Margin after dividing returns enormous number

Justas — Tue, 22 Jun 2021 12:48:14 GMT

My new formula:

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