Standard Deviation yields NaN error

[rsbin]

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,

[AlexisOlson]

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 )

[edhans]

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]

@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]

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.

[az38 · Accepted solution]

@rsbin 

try

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

[rsbin]

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.