cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## NaN Error

I created a new field, Cost/Quantity, and the output is coming up at NaN.

I was able to see the Cost by year on a bar chart and quantity by year on a bar chart fine, but when I tried to doing Cost/Quantity on a bar chart, I am not getting anything due to NaN values. How do i fix this?

Thanks

1 ACCEPTED SOLUTION
Community Champion

@Vvelardenope, turns out this is a custom column in the query editor. No DIVIDE there.

Proud to be a Super User!

12 REPLIES 12
Frequent Visitor

Hello, i have the same issue :

I calculate averages over rolling periods here are my two colums except that i have an error in the result in the column %JSA 3 months rolling : for december (the latest line) : the good value  is 0,73 (0,85+0,71+0,64)/3 but i have got : 0,65

Here are my formulas :

%JSA 3 months rolling = AVERAGEX(
DATESINPERIOD('SourceMCP'[1erJourDuMois].[Date], LASTDATE('SourceMCP'[1erJourDuMois].[Date]), -3, MONTH),
CALCULATE([TestJSA])
)
Without the divide function with 0 in TestJSA i have value return NaN

How can i fix that ?
Here a caption of my table view :

Thank you,

Chloé

Community Champion

Typically you get NaN when dividing 0 by 0.

So adjust your formula something like if Quantity = 0 then 0 else Cost / Quantity.

Specializing in Power Query Formula Language (M)
New Member

it is not working for me

@MarcelBeug wrote:

Typically you get NaN when dividing 0 by 0.

So adjust your formula something like if Quantity = 0 then 0 else Cost / Quantity.

Frequent Visitor

THANK YOU! That was so easy to fix. Thank you!

Regular Visitor

How do i get rid of this? Would I be able to replace it with 0 or null?

Community Champion

@tiffanynguyen

hi, use this:

Measure=Divide([Cost],[Quantity],0)

Lima - Peru
Frequent Visitor

super!!!! Thanks Sir!!!!

Anonymous
Not applicable

This worked for me beautifully thank you

Helper I

@Anonymous

How did you write the formula in the custom column?
I have the same issue.

Community Champion

You're dividing by zero. That's impossible. That's why you're getting the error. You need to put in an if statement to return null when [D_Act_2doTermino] is 0.

Proud to be a Super User!

Community Champion

@Vvelardenope, turns out this is a custom column in the query editor. No DIVIDE there.

Proud to be a Super User!

Community Champion

@KHorseman Thanks. Yes, Divide don't apply in query Editor.

Lima - Peru

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors