How to Solve NaN Error in Power BI?

If you use Power BI DAX (Data Analysis Expressions), you might encounter the NaN Error when dividing the column.

In this short tutorial, I will show you how to quickly solve this error in Power BI (using some easy DAX tricks).

I will also show you how to solve NaN error in Power BI with various scenarios.

What is NaN in Power BI?

In Power BI, NaN means Not a Number. It’s a special value that represents undefined numerical data. It usually occurs due to mathematical operations that don’t produce a valid numeric result.

So, when you see NaN in Power BI, it means there’s a problem with the data or the calculation that needs to be addressed.

How to find NaN Error in Power BI?

This example shows how to find NaN errors in Power BI.

Scenario:

Imagine you are analyzing sales data in Power BI for a retail company. Your dataset includes information about products, sales quantities, and revenue.

In this scenario, you might encounter NaN values when performing calculations, such as calculating the average sales quantity per product.

If a particular product had no sales during a specific period, the calculation would result in NaN because you cannot divide by zero (since there are no sales to calculate an average).

Now, I will show you how to get this error. Follow the below steps:

1. Open Power BI Desktop and load data using the Get data option. Then, you can see data in the Data pane.

2. Go to the Table view. Under the Home tab, click “New column.”

3. In the formula bar, put the below expression. Then click the Commit button.

Average Sales Quantity = 'SalesData'[Revenue]/'SalesData'[Sales Quantity]

Where:

• Average Sales Quantity = Name of the new column representing the average sales quantity for each transaction.
• ‘SalesData'[Revenue] = It represents the total revenue generated from sales.
• ‘SalesData'[Sales Quantity] = It represents the total quantity of products sold.

4. After that, you can see some rows containing NaN errors in the Average Sales Quantity column.

This way, you find NaN errors in Power BI.

Replace NaN with 0 using Power BI IFERROR Function

This example shows how to replace NaN with O using IFERROR in Power BI.

I hope you create the above example in your Power BI. To replace with 0, follow the below steps:

1. Go to the Table view. Under the Home tab, click the New column.

2. In the formula bar, put the below expression. Then click the Commit button.

Average Sales Quantity 1 = IFERROR('SalesData'[Revenue]/'SalesData'[Sales Quantity],0)

Where:

• Average Sales Quantity 1 = Name of the new column representing the average sales quantity for each transaction.
• IFERROR() = This function checks If an error occurs, it allows us to specify a value to replace the error.
• ‘SalesData'[Revenue] = It represents the total revenue generated from sales.
• ‘SalesData'[Sales Quantity] = It represents the total quantity of products sold.
• 0 = This is the value we specify to replace errors during the division operation.

3. After that, NaN errors are replaced with 0 in the Average Sales Quantity 1 column.

This way, you can replace NaN with O using IFERROR in Power BI.

Replace NaN with 0 using Power BI DIVIDE Function

In this example we see how to replace NaN with 0 using Power BI DIVIDE function.

I hope you load the above data set in your Power BI Desktop.

1. Go to the Table view. Under the Home tab, click the New column.

2. In the formula bar, put the below expression. Then click the Commit button.

Average Sales Quantity 2 = DIVIDE(SalesData[Revenue],SalesData[Sales Quantity],0)

Where:

• Average Sales Quantity 2 = This is the name given to the calculation.
• DIVIDE = A function in DAX that divides one number by another, handling cases where the divisor might be zero to prevent errors.
• ‘SalesData'[Revenue] = It represents the total revenue generated from sales.
• ‘SalesData'[Sales Quantity] = It represents the total quantity of products sold.
• 0 = This is the value we specify to replace errors during the division operation.

3. NaN errors are replaced with 0 in the Average Sales Quantity 2 column.

You can replace NaN with O using the Power BI DIVIDE Function.

Conclusion

So, these are ways to replace NaN errors (division errors) with 0 in Power BI.

This tutorial showed us about NaN errors in Power BI, how to spot them, how to change NaN to 0 using Power BI’s DIVIDE function, and how to replace NaN with 0 using IFERROR.

Moreover, you may also like some more Power BI articles:

• Access to the resource is forbidden error in Power BI
• Power BI Error: This content isn’t available