Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
rsbin
Super User
Super User

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.

NaN error.png

 

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

 

Thanks in advance and best regards,

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

@rsbin 

try

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

6 REPLIES 6
AlexisOlson
Super User
Super User

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
Super User
Super User

Can you not just filter them out like this?

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


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@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

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
az38
Community Champion
Community Champion

@rsbin 

try

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.