Getting NaN error for a number. Makes no sense

[JS_UNI4C]

Ok, so I am now totally confused. 

I have a file with a table. Three of the column names are:

• Line_Amount
• Quantity
• Oustanding_Quantity

Using the "card visual", if I select the individual columns above, the visual populates with the sum of the entire column. Great. So they are confirmed as "numbers". 

I have tried the following two ways of getting a custom column which as worked for me in the past dozens of times, but now seemingly gives a NaN error and I have no clue why since the entire column is most certainly numbers only. 

DAX:

ItemPrice = SalesLines_UNI4C[Line_Amount] / SalesLines_UNI4C[Quantity]

AmtToShip = ItemPrice * SalesLines_UNI4C[Outstanding_Quantity]

ToShip = SalesLines_UNI4C[Line_Amount] / SalesLines_UNI4C[Quantity] * SalesLines_UNI4C[Outstanding_Quantity]

Power Query:

ToShip = Value.Multiply(Value.Divide([Line_Amount],[Quantity]),[Outstanding_Quantity])

Changed column type to "Decimal"

There are no errors in Power Query editor and everything looks like a number. I am stumped as to why Power BI is telling me this isn't a number.

Any ideas?

[JS_UNI4C · Accepted solution]

Nevermind. Problem solved. Source data is rubbish. Somehow there is order quantity of zero which means the classic DIV/0# error. Corrected that and all good

[Ritaf1983 · Accepted solution]

Hi @JS_UNI4C

The NaN error usually occurs in Power BI when division by zero happens or when some of the values in the dataset are missing or not properly recognized as numbers despite appearing to be numeric. Here are a few potential causes and solutions:

1. Division by Zero:
If the Quantity column contains any zeros, your formula will attempt to divide by zero, which results in a NaN (Not a Number) error.
To avoid this, you can update your DAX to handle zeros using the DIVIDE function, which gracefully handles division by zero:
 

DAX
Copy code
ItemPrice = DIVIDE(SalesLines_UNI4C[Line_Amount], SalesLines_UNI4C[Quantity], 0) AmtToShip = ItemPrice * SalesLines_UNI4C[Outstanding_Quantity]
 

 
Alternatively, you can wrap your direct division in an IF statement:

DAX
Copy code
ToShip = IF(SalesLines_UNI4C[Quantity] <> 0, SalesLines_UNI4C[Line_Amount] / SalesLines_UNI4C[Quantity] * SalesLines_UNI4C[Outstanding_Quantity], 0)
 

 
2. Handling Null or Blank Values:
Even if the columns are numeric, there may be blank or null values causing issues.
You can use the COALESCE function in DAX to ensure you're not working with null values:
 

DAX
Copy code
ItemPrice = DIVIDE(COALESCE(SalesLines_UNI4C[Line_Amount], 0), COALESCE(SalesLines_UNI4C[Quantity], 1)) AmtToShip = ItemPrice * COALESCE(SalesLines_UNI4C[Outstanding_Quantity], 0)
 

 
This will substitute 0 for null values in Line_Amount and Outstanding_Quantity and replace null or zero in Quantity with 1 to avoid division by zero.

3. Check Data Types in Power Query:
Even though the data looks like numbers, Power Query may treat it differently due to how the data is loaded or transformed. Ensure that all relevant columns are explicitly set to a decimal number data type in Power Query.
You can also try adding a step in Power Query to explicitly convert these columns using:
 

M
Copy code
Table.TransformColumnTypes(YourTable, {{"Line_Amount", type number}, {"Quantity", type number}, {"Outstanding_Quantity", type number}})
 

4. Evaluate DAX in Smaller Sections:
Try breaking down the calculations step by step in separate measures to identify exactly where the NaN is coming from. For example, first calculate ItemPrice, then check it separately in the report, and then calculate AmtToShip to isolate the issue.
For more specific suggestions 

please share a pbix or some dummy data that keep the raw data structure with expected results?
It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

If my answer was helpful please give me a Kudos and accept as a Solution.

[BIswajit_Das]

Hello @JS_UNI4C 
It would be better to first check you column values there may be a value which is of type text or any other
If that's not the case then try the used DAX with Format() or Convert() functions to change your column datatype.
https://learn.microsoft.com/en-us/dax/format-function-dax
if still not working then you should provide the data file.
Thanks & Regards ...

[JS_UNI4C · Accepted solution]

Nevermind. Problem solved. Source data is rubbish. Somehow there is order quantity of zero which means the classic DIV/0# error. Corrected that and all good