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
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,
Solved! Go to Solution.
try
StdDev_EquipID =
VAR __table = SUMMARIZE(FILTER(GeneralStatistics, GeneralStatistics[TotalVisits] <> 0),[Date],"__EquipID",[EquipmentIDRatio])
RETURN STDEVX.P(__table,[__EquipID])
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 )
Can you not just filter them out like this?
VAR __table =
SUMMARIZE(
FILTER(
GeneralStatistics,
TotalVisits <> 0
),
[Date],
"__EquipID", [EquipmentIDRatio]
)
RETURN
STDEVX.P(
__table,
[__EquipID]
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHey, 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank 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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
102 | |
92 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
95 |