topic My formula ends in division by zero error and I just don't know why in DAX Commands and Tips

My formula ends in division by zero error and I just don't know why

ThomasSan — Tue, 02 Nov 2021 13:20:33 GMT

Dear community,

I have a rather odd problem that I have been working on for the last two hours but still fail to understand where/what exactly the error is. Apparently, "A division by zero has been encountered". But the problem is that my denominator is a fixed value that is not zero. Is there maybe a bug in DAX that leads to this outcome? And if yes, does anyone know a work around for my issue?

Here is my concrete case

I am having the following table

where the current formula for CTS is

CTS = 1.2803*sum(Table1[Number Of Invoices])

As you can see, the 1.2803 is based on column Result which itself is the result of the value in column Numerator over the value of column Denominator (The values in column Denominator, Numerator and Result are always the same which I double checked by generating a data excerpt for Excel in which I confirmed that there are absolutely no zero values.).

Accordingly, the formula for Result is

Result = Table1[Numerator]/Table1[Denominator]

I now wish to directly insert the Result value 1.2803 directly into the CTS formula so that it reads

CTS = Table1[Result]*sum(Table1[Number Of Invoices])

However, when I do this, I get the error message

The table is filtered but this should not be an issue for my calculations, right?

So can anyone please explain to me where this mysterious division by zero is occuring?

Re: My formula ends in division by zero error and I just don't know why

Greg_Deckler — Tue, 02 Nov 2021 13:29:09 GMT

ThomasSan Try this and this will help you identify where the issue is:

Result = DIVIDE(Table1[Numerator], Table1[Denominator],0)

Or use -1 or something as the alternate result to clearly identify where the division by zero is happening.

Re: My formula ends in division by zero error and I just don't know why

ThomasSan — Tue, 02 Nov 2021 13:33:48 GMT

Greg_Deckler

Hi Greg,

I have modified the forma for Result according to your input but there still seems to be no zero

Re: My formula ends in division by zero error and I just don't know why

Greg_Deckler — Tue, 02 Nov 2021 15:43:18 GMT

ThomasSan My guess is that the blanks are what are causing the issue with the divide by zero. Seems like it is quite possible that you were dividing by BLANK() essentially which caused the divide by zero error. IDK, hard to say without the data and such. Seems like the issue is solved though.

Re: My formula ends in division by zero error and I just don't know why

v-yanjiang-msft — Fri, 05 Nov 2021 02:53:23 GMT

Hi ThomasSan ,

Has your issue be solved? If not, I have some doubts, it will help to solve your problem if you can reply to me.

1.I notice that “Result” is a measure, what about “CTS”, is it a measure or column?

2.When does the DAX error “A division by zero has been encountered” occur? Does it occur after you run “Result” or “CTS”?

3.What is the error reminder when you modify the DAX to “DIVIDE(Table1[Numerator], Table1[Denominator],0)”?

4.Is “Numerator” and “Denominator” your original data or calculated data?

Best Regards,
Community Support Team _ kalyj

Re: My formula ends in division by zero error and I just don't know why

ThomasSan — Mon, 08 Nov 2021 14:55:50 GMT

Hi v-yanjiang-msft ,

unfortunately, my problem has not been solved, yet. To your questions:

1. CTS is a measure.

2. No. Having "Result" only in a column consistently delivers the same value (here 1.2803€) and curiously does not deliver this error message. This is as expected as the denominator and nominator also stay consitent in each row.

3. The error vanishes but my DAX formula behind Curve Product is not working correctly (sidenote: it is a running total of all Gross Profit values up until each row. I have created based on the response I got from an earlier question here in the community (https://community.powerbi.com/t5/Desktop/Cumulative-Totals-from-largest-to-lowest-and-without-date/m-p/2128836#M786892))

4. The formula are "Numerator = Table1[Total Costs]" and "Denominator = CALCULATE ( SUM ( Table1[Number Of Invoices]), ALLSELECTED ( Table1) )". The latter is supposed to give me the total number of invoices in order to create the average costs. These average costs per invoice are then used to multiply the average cost of invoices with the number of invoice per product (i.e. how many times has the product been invoiced)

All in all, this is exactly my big mystery here. While PowerBI is perfectly able to calculate each element of my large formula (which is CTS = (Table1[Numerator]/Table1[Denominator] )*sum(Table1[Number Of Invoices]), I am unable to do so by inserting this large formula as CTS since it will result in the above mentioned error message. Even stranger, I fail to understand where exactly a division by 0 is happening as all denominator values are constant (here 3.243).

PS: I just noticed it is not correct to have denominator in Euros as it is just the total number of invoices. Naturally, that is not a monetary value. However, this should have no effect on my described problem here

Re: My formula ends in division by zero error and I just don't know why

v-yanjiang-msft — Wed, 10 Nov 2021 09:52:59 GMT

Hi ThomasSan ，

According to your description, because the “Result” formula works fine, I think there’s no zero in your “Denominator” column, I guess maybe it’s the aggregation cause your problem. You can try to create a new column like this:

Column=[Result]

CTS = Table1[Column]*sum(Table1[Number Of Invoices])

Best Regards,
Community Support Team _ kalyj