HELP - Standard Deviation

[M0n5ta09]

Hi everyone,

I've been trying for hours to calculate the Standard Deviation by month and by year based on _Faults per Day but getting nowhere.

I'm expecting the STDEV to be around 0.75 for 2017 and 0.65 for 2018.

Eventually I will be using the STDEV to calculate Lower and Upper Control Limits to visualise on a line chart against _Faults per Day.

Measures:

• Faults = DISTINCTCOUNT('Full WO Extract'[FaultNum])     // Distinctcount of Fault Numbers
• _Selected EOMonth = SELECTEDVALUE('Full WO Extract'[c_EOMonth])     // Number of Days for Month
• _Faults per Day = DIVIDE( 'Full WO Extract'[Faults], 'Full WO Extract'[_Selected EOMonth])
• _Mean - Faults per Day = DIVIDE(SUMX('Full WO Extract','Full WO Extract'[_Faults per Day]),12)

 Any help will be most appreciated. Thank you.

[M0n5ta09]

Thanks for your replies @ALLUREAN and @amitchandak 

_Faults per Day and _Mean - Faults per Day are both measures, I modified each to;

__STDEV = CALCULATE(STDEVX.P('EXTRACT','EXTRACT'[_Faults per Day]), ALLEXCEPT('DATES',DATES[Year]))

and

STDEV (LongTerm) = STDEVX.S('EXTRACT','EXTRACT'[_Faults per Day])

but both produce a 0.00 result.

Year	MonthName	_Selected EOMonth	_DistinctWO	_Faults per Day	_Mean - Faults per Day
2017	Jan	31	212	6.84	0.57
2017	Feb	28	193	6.89	0.57
2017	Mar	31	217	7.00	0.58
2017	Apr	30	249	8.30	0.69
2017	May	31	229	7.39	0.62
2017	Jun	30	259	8.63	0.72
2017	Jul	31	271	8.74	0.73
2017	Aug	31	258	8.32	0.69
2017	Sep	30	224	7.47	0.62
2017	Oct	31	241	7.77	0.65
2017	Nov	30	212	7.07	0.59
2017	Dec	31	272	8.77	0.73

I'm looking for the Standard Deviation for _Faults per Day. In Excel, STDEV for _Faults per Day returns 0.75.

Thank you.

[ALLUREAN]

Hi, 

I used the measure that proposed earlier and get this result, check the file here. For 2017 STDEV is 0.75. I just remove the filter for month (ALL function) if you want to have the same value in each month in case you will show months also in the table

https://www.dropbox.com/s/pafc51sst6ggvuu/STDEV_%28allure-analytics.com%29.pbix?dl=0

[ALLUREAN · Accepted solution]

Can you try this measure:

STDEV (LongTerm) = STDEV.S([_Faults per Day])

Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

Appreciate your Kudos !!!

https://allure-analytics.com/

[M0n5ta09]

Many thanks @ALLUREAN.

Once I got the values into a column in the table opposed to using a measure, it worked perfectly.

[amitchandak]

@M0n5ta09 , Try like

calculate(Stddev.p(Table[_Mean - Faults per Day]), allexcept(table[Year]))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.