cancel
Showing results 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

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.

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
Community Champion

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
6 REPLIES 6
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 )``````
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 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
Super User

@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

Super User

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 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
Community Champion

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

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.

Announcements

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors