Forum Discussion

carter's avatar
carter
Frequent Visitor
8 years ago

NaN Error only sometimes???

Hello!

 

I using the following dax function to find the average Std Dev across all groups. 

 

Standard deviation of VALUES average per GROUP = 
AVERAGEX(
KEEPFILTERS(VALUES('GROUP')),
CALCULATE(STDEV.P('VALUES'))
)

 

When I use this measure on a number of categories I recieve the following table:

 

There is no blanks so I am having difficulty finding out where this NaN is coming from. 

 

When I filter the data to a category with a NaN value I recieve the following table and surprise the NaN value disappears.

 

 

How might I fix this?

 

 

Any help would be greatly appreciated!

Need Help

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi carter,

     

    Can you share a sample of your data? The "NaN" could be raised by the function STDEV.P.

     

    Best Regards,

    Dale

    • carter's avatar
      carter
      Frequent Visitor
      8 years ago

       

      Hi Dale (v-jiascu-msft)

       

      Here is some sample data:

       

      Thanks,

      Carter

       

      CategoryGroupValue
      1.043-45000-45010.1
      1.043-45000-45002.4
      1.043-45000-45002
      1.043-45000-44999
      1.043-45000-44992.1
      1.044-45000-45010.1
      1.044-45000-44999.3
      2.001-45000-45010.1
      2.001-45000-45000.8
      2.001-45000-44999.6
      2.004-45000-45010.1
      2.004-45000-45006.4
      2.004-45000-45004.3
      2.004-45000-44999.6
      2.008-45000-45010.1
      2.008-45000-45003.6
      2.008-45000-45003
      2.008-45000-45002.8
      2.008-45000-45002.6
      2.008-45000-44997.3
      1.043-40000-40013.5
      1.043-40000-40004.1
      1.043-40000-40003.4
      1.043-40000-40000.8
      1.043-40000-39996.9
      1.043-40000-39996.3
      1.043-40000-39995.4
      1.043-40000-39995.1
      1.043-40000-39993.2
      1.044-40000-40013.5
      1.044-40000-40000.4
      1.044-40000-39996.9
      2.001-40000-40013.5
      2.001-40000-40002.7
      2.004-40000-40013.5
      2.004-40000-39999.2
      2.008-40000-40013.5
      2.008-40000-40004
      2.008-40000-40002.7
      2.008-40000-40002
      2.008-40000-39996.9
      1.043-30000-30020.4
      1.043-30000-30007.3
      1.043-30000-30002
      1.043-30000-29998.9
      1.043-30000-29994.6
      1.043-30000-29990.4
      1.044-30000-30020.4
      2.001-30000-30020.4
      2.001-30000-29998.2
      2.001-30000-29997.2
      2.001-30000-29996.8
      2.004-30000-30020.4
      2.004-30000-30008.3
      2.008-30000-30020.4
      2.008-30000-30006.4
      2.008-30000-30005.9
      2.008-30000-30003.5
      2.008-30000-30001.2
      2.008-30000-30001
      2.008-30000-29999
      2.008-30000-29998.5
      1.038-20000-19999.9
      1.038-20000-19999.3
      1.038-20000-19999.2
      1.038-20000-19997.2
      1.038-20000-19995.7
      1.038-20000-19993
      1.039-20000-20000.8
      1.039-20000-19999.9
      1.039-20000-19999.1
      1.039-20000-19999
      1.039-20000-19997.2
      1.039-20000-19996.5
      1.039-20000-19993.9
      1.039-20000-19993
      1.039-20000-19991.3
      1.04-20000-20001.5
      1.04-20000-20000.6
      1.04-20000-19999.9
      1.04-20000-19998.2
      1.04-20000-19997.2
      1.04-20000-19988
      1.042-20000-19999.9
      1.042-20000-19997.7
      1.043-20000-19999.9
      1.043-20000-19997.7
      1.043-20000-19997.2
      1.043-20000-19996.2
      1.043-20000-19995.2
      1.043-20000-19989.5
      1.044-20000-19999.9
      1.044-20000-19997.2
      2.001-20000-19999.9
      2.001-20000-19999.3
      2.001-20000-19997.4
      2.001-20000-19997.2
      2.001-20000-19997.2
      2.001-20000-19995.7
      2.004-20000-19999.9
      2.004-20000-19997.2
      2.004-20000-19996.9
      2.005-20000-19999.9
      2.007-20000-19999.9
      2.007-20000-19998
      2.007-20000-19499.7
      2.008-20000-20027.2
      2.008-20000-20003.4
      2.008-20000-20001.7
      2.008-20000-20001.6
      2.008-20000-20000.2
         
      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi carter,

         

        Can you share the pbix file? You can delete the private parts first. I can't reproduce the same result with your sample data. Just see from the formula √[∑(x - x̃)²/n], it seems no possibilities to get such results.

         

        Best Regards,

        Dale