Power BI nan error (Not a number) while dividing by Zero

SharePoint Online training courses

In this Microsoft Power BI Tutorial, We will discuss how we can fix error “Power BI NaN (Not a number) while dividing by Zero“.

Power BI NaN (Not a number) Error while dividing by Zero

In Power BI Desktop, I have a table named as Income Tax Rates which has below columns as:

  • Country (Choice Data type)
  • Net Wage Earnings After Tax (Currency Data type)
  • Income Tax Rate (Number Data type)
  • Employee Social Security Contributions (Number Data type)
  • Net Wage Bonus (Currency Data type)

As per my requirement, I wanted to calculate the total Income Tax Variance (Income Tax Rate/Employee Social Security Contributions) of Iceland whose rank is 3 like the below screenshot.

NaN error in Power BI

In the Power BI Desktop, Get the SharePoint List from SharePoint Online Site to your Power BI Desktop. Follow the below link to get the SharePoint List to Power BI Desktop:

Load SharePoint List to Power BI Desktop

Now format the columns which are having different Data types in Power BI Desktop. In the below screenshot, I have changed the below column Data types as:

  • Employee Social Security Contributions (Whole Number)
  • Income Tax Rate (Whole Number)
  • Net Wage Bonus (Whole Number)
  • Net Wage Earnings After Tax (Whole Number)
power bi NaN error

Now I have retrieved the Column (Employee Social Security Contribution) value of Country (Iceland) by using the below Power BI measure formula:

if nan power query

Similarly, I have retrieved the Column (Income Tax Rate) value of Country (Iceland) by using the below Power BI measure formula:

nan error power bi

And then I have divided these above columns to get the total variance of Country (Iceland). For that, I have used this below Power BI measure formula.

what is nan in power bi

While I have added the Measure [Total Income Variance (Iceland)] in a Card visual, then the result was appearing as NaN which you can see in the below screenshot.

nan in power bi

Power BI NaN (Not a number) while dividing by Zero

Basically, In the Power BI Desktop, We are getting the error NaN when we are dividing the value as 0 by 0.

To resolve this NaN issue, We have to follow these below steps:

Step-1:

First of all, Go to the Home tab and then click on Edit Queries from the ribbon. Select Edit Queries from the drop-down as shown below.

power bi replace nan with blank

Step-2:

Once you will click on the Edit Queries option, then the below query editor window will appear. In this window, Go to the Add Column tab and then Click on Custom Column.

replace nan with 0 power query

Step-3:

Now a Custom Column window will appear where you have to write the Custom Column Query formula for the error NaN.

Enter a new column name and put the below Query formula in Custom column formula field:

Income Variance= if Number.IsNaN(0/0) then 0 else 1
  • Here, New column name = Income Variance
  • Custom column formula = if Number.IsNaN(0/0) then 0 else 1

By using a double click, you can insert the Available columns if you want otherwise click on OK Button as like below screenshot.

what does nan in power bi mean

Step-4:

Now you can see the new custom column(Income Variance) will be created in the Query Editor window.

After that, do not forget to click on the Close & Apply button. Go to the Home tab and click on Close & Apply option as like the below figure.

what does nan mean in power bi

Step-5:

Now to test the new custom column, Take a Card under the Visualizations section and insert the custom column (Income Variance) into the Fields section of the card. Then in the Card, you can see the value will be appearing as 0.

As per my requirement, I wanted the Income Variance field as Percentage variance, that’s why I have formatted the data type as Percentage.

handle nan in power bi

Also, You may like following Power BI tutorials:

Hence in this Microsoft Power BI Tutorial, We discussed how to fix error “Power BI NaN (Not a number) while dividing by Zero“.

>