Calculated column returning NaN and infinity

[Anonymous]

I'm trying to create a calculated column in my dataset using the following formula:

PercentHoursRemaining = ('column1'[Hours_Actual]) / ('column2'[hours_budget])

Both columns are of data type Decimal Number and have very few non-zero values. The resulting calculated column, however, returns a vast majority of values as "NaN' or 'infinity'. I'm fairly confident this isn't an issue of diving by zero nor is it a data type issue as I've exhausted both of those possibilities.

I have also tried entering in the above formula into the "New Measure' function instead but received the following error:

A single value for column 'Hours_Actual' in table 'v_rpt_Project' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I'm a new PowerBI user so these are all the things I can think of to troubleshoot this. Any help would be much appreciated.

Thanks in advance!

[rockingmark]

Cool,  Thanks v-lili6-msft.  The DIVIDE Function worked a treat!!!!

[iris_kinki128]

I have a problem the opposite that i would like to show "NaN" in calculated column.

I have the data in excel below:

Site Code	TA Score
B0795	0.90
STUC_1391	n/a

In Power BI, "n/a" turns out showing "blank" in "Data" view and considered as "0", but actually i want to show it as "NaN".

@v-lili6-msft What i can do to change it?

or any people can advise?

Many thanks!!!!

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer, it is static.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, You could use DIVIDE Function in your formula as below:

https://docs.microsoft.com/en-us/dax/divide-function-dax

https://docs.microsoft.com/en-us/power-bi/guidance/dax-divide-function-operator

Column=DIVIDE('column1'[Hours_Actual],'column2'[hours_budget],0)

and for measure you could use this formula

Measure=DIVIDE(SUM('column1'[Hours_Actual]),SUM('column2'[hours_budget]),0)

Regards,

Lin

[Jorgast]

Have you looked at the IfError?

If the calculation shows an error then it will show whatever you want it to show, otherwise it will show you the result of the division.

https://docs.microsoft.com/en-us/dax/iferror-function-dax

PercentHoursRemaining = IFERROR ('column1'[Hours_Actual]) / ('column2'[hours_budget], 0)