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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.