Forum Discussion

rsbin's avatar
rsbin
Community Champion
6 years ago
Solved

Standard Deviation yields NaN error

Good Afternoon All & Greg_Deckler 

 

Following up on my previous thread wherein I used the following formula to calculate Standard Deviation:

 

StdDev_EquipID =
VAR __table = SUMMARIZE(GeneralStatistics,[Date],"__EquipID",[EquipmentIDRatio])
RETURN STDEVX.P(__table,[__EquipID])

 

There are several data points where my denominator (TotalVisits) is a zero and hence believe this is what is giving me an "NaN" error in my STDDEV calculation.

 

Would appreciate advice on how to correct the above formula to ignore the line records where TotalVisits = 0.

 

Thanks in advance and best regards,

Need Help
  • az38's avatar
    az38
    6 years ago

    rsbin 

    try

    StdDev_EquipID =
VAR __table = SUMMARIZE(FILTER(GeneralStatistics, GeneralStatistics[TotalVisits] <> 0),[Date],"__EquipID",[EquipmentIDRatio])
RETURN STDEVX.P(__table,[__EquipID])

6 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    rsbin 

    try

    StdDev_EquipID =
VAR __table = SUMMARIZE(FILTER(GeneralStatistics, GeneralStatistics[TotalVisits] <> 0),[Date],"__EquipID",[EquipmentIDRatio])
RETURN STDEVX.P(__table,[__EquipID])
    • rsbin's avatar
      rsbin
      Community Champion
      6 years ago

      Thank you az38 !!   Appreciate the fast response.  I knew I needed a filter in there somewhere, just couldn't get the syntax right.

       

      Thanks again and All the Best.

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    Can you not just filter them out like this?

    VAR __table =
    SUMMARIZE(
        FILTER(
            GeneralStatistics,
            TotalVisits <> 0
        ),
        [Date],
        "__EquipID", [EquipmentIDRatio]
    )
RETURN
    STDEVX.P(
        __table,
        [__EquipID]
    )
    • rsbin's avatar
      rsbin
      Community Champion
      6 years ago

      edhans   Thanks kindly for the reply.  Same solution as az38     ....he just beat you to it.

       

      Appreciate you chiming in and confirming solution.

       

      All the Best

      • edhans's avatar
        edhans
        Community Champion
        6 years ago

        Hey, no problem rsbin - he was quicker with the enter key!

         

        Kudos/Thumbs up are still welcome. In any event, glad your problem is resolved and your project is moving forward.

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    6 years ago

    The formula is correct. Standard deviation is undefined for a zero denominator.

     

    If you want to show something else instead of NaN you can write that rule into your measure:

     

    StdDev_EquipID =
VAR __table =
    SUMMARIZE ( GeneralStatistics, [Date], "__EquipID", [EquipmentIDRatio] )
VAR SD =
    STDEVX.P ( __table, [__EquipID] )
RETURN
    IF ( ISERROR ( SD ), BLANK (), SD )