Forum Discussion
Calculated column returning NaN and infinity
I'm trying to create a calculated column in my dataset using the following formula:
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
4 Replies
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
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)
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!!!!
Cool, Thanks v-lili6-msft. The DIVIDE Function worked a treat!!!!
